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:
but I want to change the query that give me the output like below:
sql oracle oracle-sqldeveloper
edited Nov 17 at 10:14
William Robertson
7,87922033
asked Nov 17 at 8:57
navid sedigh
718
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:
but I want to change the query that give me the output like below:
sql oracle oracle-sqldeveloper
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? Orrate_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
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:
but I want to change the query that give me the output like below:
sql oracle oracle-sqldeveloper
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:
but I want to change the query that give me the output like below:
sql oracle oracle-sqldeveloper
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
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
718
What determines the sequence reset? 'rate` alone? Orrate_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? Orrate_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
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 |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
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
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
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
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
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
@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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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