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 Go to next message
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 #414342 is a reply to message #414338] Tue, 21 July 2009 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
want the result set to be as below:

The result set of what?

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #414346 is a reply to message #414338] Tue, 21 July 2009 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> Is there a better way to continue the rank sequence?
IMO, yes it is better to RANK only when doing SELECT for data presentation.

Rows in a table have no inherent order.
Trying to store RANK within table is an unwise thing to do.
Re: Per Group ranking - how to continue rank numbers when new rows are inserted in a group? [message #414347 is a reply to message #414338] Tue, 21 July 2009 13:14 Go to previous messageGo to next message
anya_03
Messages: 7
Registered: June 2009
Junior Member
Thanks Michel. This seems to work. I will run it by my data.

Hi Blackswan, I need to uniquely identify each line for each Claim_id. If not RANK, how do you suggest I do it?
Re: Per Group ranking - how to continue rank numbers when new rows are inserted in a group? [message #414348 is a reply to message #414338] Tue, 21 July 2009 13:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I need to uniquely identify each line for each Claim_id.
If uniqueness is the only real requirement, then it could be done with a single SEQUENCE.
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 Go to previous messageGo to next message
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
Re: Per Group ranking - how to continue rank numbers when new rows are inserted in a group? [message #414520 is a reply to message #414351] Wed, 22 July 2009 07:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why not try a two stage process - insert the rows with a blank claim_line_id, and then update them.

Previous Topic: Performance Speed for select from USER_ROLE_PRIVS (merged)
Next Topic: getting the date difference in minutes
Goto Forum:
  


Current Time: Sat Feb 15 08:28:58 CST 2025