Add a sequence column in a query

up vote
0
down vote

favorite

I have a query like below

select ref_leger_code,rate,sum(balance),to_char(due_date,'yyyymm')

from tbl_value_temp

group by ref_leger_code,rate,to_char(due_date,'yyyymm');

and the output is:
output

but I want to change the query that give me the output like below:
output2

edited Nov 17 at 10:14

William Robertson

7,87922033

asked Nov 17 at 8:57

navid sedigh

718

What determines the sequence reset? 'rate` alone? Or rate_ledger_code, rate? Or something else? Please provide us with the business rules you want to implement instead of expecting us to reverse engineer them from your output.
– APC
Nov 17 at 10:07

add a comment |

up vote
0
down vote

favorite

I have a query like below

select ref_leger_code,rate,sum(balance),to_char(due_date,'yyyymm')

from tbl_value_temp

group by ref_leger_code,rate,to_char(due_date,'yyyymm');

and the output is:
output

but I want to change the query that give me the output like below:
output2

edited Nov 17 at 10:14

William Robertson

7,87922033

asked Nov 17 at 8:57

navid sedigh

718

What determines the sequence reset? 'rate` alone? Or rate_ledger_code, rate? Or something else? Please provide us with the business rules you want to implement instead of expecting us to reverse engineer them from your output.
– APC
Nov 17 at 10:07

add a comment |

up vote
0
down vote

favorite

I have a query like below

select ref_leger_code,rate,sum(balance),to_char(due_date,'yyyymm')

from tbl_value_temp

group by ref_leger_code,rate,to_char(due_date,'yyyymm');

and the output is:
output

but I want to change the query that give me the output like below:
output2

edited Nov 17 at 10:14

William Robertson

7,87922033

asked Nov 17 at 8:57

navid sedigh

718

I have a query like below

select ref_leger_code,rate,sum(balance),to_char(due_date,'yyyymm')

from tbl_value_temp

group by ref_leger_code,rate,to_char(due_date,'yyyymm');

and the output is:
output

but I want to change the query that give me the output like below:
output2

sql oracle oracle-sqldeveloper

edited Nov 17 at 10:14

William Robertson

7,87922033

asked Nov 17 at 8:57

navid sedigh

718

edited Nov 17 at 10:14

William Robertson

7,87922033

asked Nov 17 at 8:57

navid sedigh

718

edited Nov 17 at 10:14

William Robertson

7,87922033

edited Nov 17 at 10:14

William Robertson

7,87922033

edited Nov 17 at 10:14

William Robertson

7,87922033

asked Nov 17 at 8:57

navid sedigh

718

asked Nov 17 at 8:57

navid sedigh

718

asked Nov 17 at 8:57

navid sedigh

718

What determines the sequence reset? 'rate` alone? Or rate_ledger_code, rate? Or something else? Please provide us with the business rules you want to implement instead of expecting us to reverse engineer them from your output.
– APC
Nov 17 at 10:07

add a comment |

What determines the sequence reset? 'rate` alone? Or rate_ledger_code, rate? Or something else? Please provide us with the business rules you want to implement instead of expecting us to reverse engineer them from your output.
– APC
Nov 17 at 10:07

What determines the sequence reset? 'rate` alone? Or rate_ledger_code, rate? Or something else? Please provide us with the business rules you want to implement instead of expecting us to reverse engineer them from your output.
– APC
Nov 17 at 10:07

add a comment |

1 Answer
1

active

oldest

votes

up vote
2
down vote

accepted

I suspect that you are looking about numbering the rows based on the rate so use an analytic function like this :

 select ref_leger_code, rate, sumbalance, due_date,

        ROW_NUMBER() OVER (PARTITION BY rate ORDER BY due_date asc ) AS sequence  

 from   (

          select ref_leger_code, rate, sum(balance) sumbalance, to_char(due_date,'yyyymm') due_date

          from   tbl_value_temp

          group by ref_leger_code, rate, to_char(due_date,'yyyymm')

        );

edited Nov 17 at 13:30

William Robertson

7,87922033

answered Nov 17 at 9:20

Dypso

342210

It gives error: ORA-00924: missing BY keyword 00924. 00000 - "missing BY keyword" *Cause: *Action: Error at Line: 11 Column: 35
– navid sedigh
Nov 17 at 9:30

@navidsedigh - Dypso missed the BY after the PARTITION keyword. I have corrected their code, please try it now.
– APC
Nov 17 at 10:04

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53349704%2fadd-a-sequence-column-in-a-query%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
2
down vote

accepted

I suspect that you are looking about numbering the rows based on the rate so use an analytic function like this :

 select ref_leger_code, rate, sumbalance, due_date,

        ROW_NUMBER() OVER (PARTITION BY rate ORDER BY due_date asc ) AS sequence  

 from   (

          select ref_leger_code, rate, sum(balance) sumbalance, to_char(due_date,'yyyymm') due_date

          from   tbl_value_temp

          group by ref_leger_code, rate, to_char(due_date,'yyyymm')

        );

edited Nov 17 at 13:30

William Robertson

7,87922033

answered Nov 17 at 9:20

Dypso

342210

It gives error: ORA-00924: missing BY keyword 00924. 00000 - "missing BY keyword" *Cause: *Action: Error at Line: 11 Column: 35
– navid sedigh
Nov 17 at 9:30

@navidsedigh - Dypso missed the BY after the PARTITION keyword. I have corrected their code, please try it now.
– APC
Nov 17 at 10:04

add a comment |

up vote
2
down vote

accepted

I suspect that you are looking about numbering the rows based on the rate so use an analytic function like this :

 select ref_leger_code, rate, sumbalance, due_date,

        ROW_NUMBER() OVER (PARTITION BY rate ORDER BY due_date asc ) AS sequence  

 from   (

          select ref_leger_code, rate, sum(balance) sumbalance, to_char(due_date,'yyyymm') due_date

          from   tbl_value_temp

          group by ref_leger_code, rate, to_char(due_date,'yyyymm')

        );

edited Nov 17 at 13:30

William Robertson

7,87922033

answered Nov 17 at 9:20

Dypso

342210

It gives error: ORA-00924: missing BY keyword 00924. 00000 - "missing BY keyword" *Cause: *Action: Error at Line: 11 Column: 35
– navid sedigh
Nov 17 at 9:30

@navidsedigh - Dypso missed the BY after the PARTITION keyword. I have corrected their code, please try it now.
– APC
Nov 17 at 10:04

add a comment |

up vote
2
down vote

accepted

I suspect that you are looking about numbering the rows based on the rate so use an analytic function like this :

 select ref_leger_code, rate, sumbalance, due_date,

        ROW_NUMBER() OVER (PARTITION BY rate ORDER BY due_date asc ) AS sequence  

 from   (

          select ref_leger_code, rate, sum(balance) sumbalance, to_char(due_date,'yyyymm') due_date

          from   tbl_value_temp

          group by ref_leger_code, rate, to_char(due_date,'yyyymm')

        );

edited Nov 17 at 13:30

William Robertson

7,87922033

answered Nov 17 at 9:20

Dypso

342210

I suspect that you are looking about numbering the rows based on the rate so use an analytic function like this :

 select ref_leger_code, rate, sumbalance, due_date,

        ROW_NUMBER() OVER (PARTITION BY rate ORDER BY due_date asc ) AS sequence  

 from   (

          select ref_leger_code, rate, sum(balance) sumbalance, to_char(due_date,'yyyymm') due_date

          from   tbl_value_temp

          group by ref_leger_code, rate, to_char(due_date,'yyyymm')

        );

edited Nov 17 at 13:30

William Robertson

7,87922033

answered Nov 17 at 9:20

Dypso

342210

edited Nov 17 at 13:30

William Robertson

7,87922033

edited Nov 17 at 13:30

William Robertson

7,87922033

edited Nov 17 at 13:30

William Robertson

7,87922033

answered Nov 17 at 9:20

Dypso

342210

answered Nov 17 at 9:20

Dypso

342210

answered Nov 17 at 9:20

Dypso

342210

It gives error: ORA-00924: missing BY keyword 00924. 00000 - "missing BY keyword" *Cause: *Action: Error at Line: 11 Column: 35
– navid sedigh
Nov 17 at 9:30

@navidsedigh - Dypso missed the BY after the PARTITION keyword. I have corrected their code, please try it now.
– APC
Nov 17 at 10:04

add a comment |

It gives error: ORA-00924: missing BY keyword 00924. 00000 - "missing BY keyword" *Cause: *Action: Error at Line: 11 Column: 35
– navid sedigh
Nov 17 at 9:30

@navidsedigh - Dypso missed the BY after the PARTITION keyword. I have corrected their code, please try it now.
– APC
Nov 17 at 10:04

It gives error: ORA-00924: missing BY keyword 00924. 00000 - "missing BY keyword" *Cause: *Action: Error at Line: 11 Column: 35
– navid sedigh
Nov 17 at 9:30

@navidsedigh - Dypso missed the BY after the PARTITION keyword. I have corrected their code, please try it now.
– APC
Nov 17 at 10:04

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Argthtjtr