Home » SQL & PL/SQL » SQL & PL/SQL » Per Group ranking - how to continue rank numbers when new rows are inserted in a group? (Oracle 10g)
Per Group ranking - how to continue rank numbers when new rows are inserted in a group? [message #414338] |
Tue, 21 July 2009 12:32  |
anya_03
Messages: 7 Registered: June 2009
|
Junior Member |
|
|
Hi,
My query is as below.
create table test_stg
(
Source_Cd varchar2(20),
Claim_Id varchar2(20),
Line_id varchar2(20),
Line_Typ_id varchar2(20),
Line_Suf_id varchar2(20)
)
insert into test_stg values ('A','12345','1','01','0');
insert into test_stg values ('A','12345','1','00','1');
insert into test_stg values ('A','22001','1','01','0');
insert into test_stg values ('A','22001','1','00','1');
insert into test_stg values ('A','22001','2','01','0');
insert into test_stg values ('A','22001','2','00','1');
insert into test_stg values ('A','22001','3','01','0');
create table test_tgt
(
Source_Cd varchar2(20),
Claim_Id varchar2(20),
Line_id varchar2(20),
Line_Typ_id varchar2(20),
Line_Suf_id varchar2(20),
claim_line_id number(5)
);
insert into test_tgt
select source_cd,claim_id,line_id,line_typ_id,line_suf_id,
rank() over (partition by claim_id order by source_cd,
claim_id,line_id,line_typ_id,line_suf_id)claim_line_id from test_stg;
delete test_stg;
insert into test_stg values ('A','12345','2','01','0');
insert into test_stg values ('A','12345','2','00','1');
insert into test_stg values ('A','22001','3','00','1');
insert into test_stg values ('A','22001','4','01','0');
insert into test_stg values ('A','22001','4','00','1');
insert into test_stg values ('A','22001','5','01','0');
insert into test_stg values ('A','22001','5','00','1');
SQL> select * from test_stg;
SOURCE_CD CLAIM_ID LINE_ID LINE_TYP_ID LINE_SUF_ID
-------------------- -------------------- -------------------- -------------------- --------------------
A 12345 2 01 0
A 12345 2 00 1
A 22001 3 00 1
A 22001 4 01 0
A 22001 4 00 1
A 22001 5 01 0
A 22001 5 00 1
SQL> select * from test_tgt;
SOURCE_CD CLAIM_ID LINE_ID LINE_TYP_ID LINE_SUF_ID CLAIM_LINE_ID
-------------------- -------------------- -------------------- -------------------- -------------------- -------------
A 12345 1 00 1 1
A 12345 1 01 0 2
A 22001 1 00 1 1
A 22001 1 01 0 2
A 22001 2 00 1 3
A 22001 2 01 0 4
A 22001 3 01 0 5
Now when I insert the new values of test_stg in test_tgt, I want it to continue on the rank i.e. I want the result set to be as below:
SOURCE_CD CLAIM_ID LINE_ID LINE_TYP_ID LINE_SUF_ID CLAIM_LINE_ID
-------------------- -------------------- -------------------- -------------------- -------------------- -------------
A 12345 1 00 1 1
A 12345 1 01 0 2
A 12345 2 00 1 3
A 12345 2 01 0 4
Can someone please advise the best possible approach to do the same considering that the data in test_tgt is approximately a million and less than a lac in staging!
Thanks!
[Updated on: Tue, 21 July 2009 12:53] by Moderator Report message to a moderator
|
|
|
|
Re: Per Group ranking - how to continue rank numbers when new rows are inserted in a group? [message #414343 is a reply to message #414338] |
Tue, 21 July 2009 13:03   |
anya_03
Messages: 7 Registered: June 2009
|
Junior Member |
|
|
I need help to write an optimized query which will give me this resultset. How can I best do that?
e.g. for the claim_id = '12345', I can write the query as:
insert into test_tgt
select a.source_cd,a.claim_id,a.line_id,a.line_typ_id,a.line_suf_id,a.new_rank + b.max_old_rank
from (select source_cd,claim_id,line_id,line_typ_id,line_suf_id,
rank() over (partition by claim_id order by source_cd,claim_id,line_id,line_typ_id,line_suf_id)new_rank from test_stg)a,
(select max(claim_line_id)max_old_rank from test_tgt where claim_id = '12345')b
where claim_id = '12345'
which gives me what I want as below:
SOURCE_CD CLAIM_ID LINE_ID LINE_TYP_ID LINE_SUF_ID CLAIM_LINE_ID
-------------------- -------------------- -------------------- -------------------- -------------------- -------------
A 12345 1 00 1 1
A 12345 1 01 0 2
A 12345 2 00 1 3
A 12345 2 01 0 4
But is this a good approach? Is there a better way to continue the rank sequence?
Thanks!
|
|
|
Re: Per Group ranking - how to continue rank numbers when new rows are inserted in a group? [message #414344 is a reply to message #414338] |
Tue, 21 July 2009 13:05   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> insert into test_tgt
2 select source_cd,s.claim_id,line_id,line_typ_id,line_suf_id,
3 t.cnt+rank() over (partition by s.claim_id order by source_cd,line_id,line_typ_id,line_suf_id)
4 from test_stg s, (select claim_id, count(*) cnt from test_tgt group by claim_id) t
5 where t.claim_id=s.claim_id
6 /
7 rows created.
SQL> select * from test_tgt order by 1, 2, CLAIM_LINE_ID
2 /
SOURCE_CD CLAIM_ID LINE_ID LINE_TYP_ID LINE_SUF_ID CLAIM_LINE_ID
-------------------- -------------------- -------------------- -------------------- -------------------- -------------
A 12345 1 00 1 1
A 12345 1 01 0 2
A 12345 2 00 1 3
A 12345 2 01 0 4
A 22001 1 00 1 1
A 22001 1 01 0 2
A 22001 2 00 1 3
A 22001 2 01 0 4
A 22001 3 01 0 5
A 22001 3 00 1 6
A 22001 4 00 1 7
A 22001 4 01 0 8
A 22001 5 00 1 9
A 22001 5 01 0 10
14 rows selected.
Regards
Michel
|
|
|
|
|
|
Re: Per Group ranking - how to continue rank numbers when new rows are inserted in a group? [message #414351 is a reply to message #414338] |
Tue, 21 July 2009 13:37   |
anya_03
Messages: 7 Registered: June 2009
|
Junior Member |
|
|
Well, I need it to be unique only within a particular Claim_id.
I still need the unique sequence to reset when the Claim_id changes.
Like in the dataset below, I do not want the Claim_line_Id to be a sequence from 1 to 14. I need it the way it is generated by Michel. How can one do it through a sequence? Is using rank not the best approach?
Thanks!
SQL> select * from test_tgt order by 1, 2, CLAIM_LINE_ID
2 /
SOURCE_CD CLAIM_ID LINE_ID LINE_TYP_ID LINE_SUF_ID CLAIM_LINE_ID
-------------------- -------------------- -------------------- -------------------- -------------------- -------------
A 12345 1 00 1 1
A 12345 1 01 0 2
A 12345 2 00 1 3
A 12345 2 01 0 4
A 22001 1 00 1 1
A 22001 1 01 0 2
A 22001 2 00 1 3
A 22001 2 01 0 4
A 22001 3 01 0 5
A 22001 3 00 1 6
A 22001 4 00 1 7
A 22001 4 01 0 8
A 22001 5 00 1 9
A 22001 5 01 0 10
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 08:28:58 CST 2025
|