Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Rows Returned but Only Want One Based on stat_cde
Multiple Rows Returned but Only Want One Based on stat_cde [message #319725] Mon, 12 May 2008 16:09 Go to next message
anewhart21
Messages: 2
Registered: May 2008
Junior Member
I am at a road block and am hoping someone can help and that I ask this correctly and it makes sense.

I have a select statement that will return multiple rows for the same primary_id but I only want one row depending on the stat_cde.

With a normal select, nothing fancy, statement I get this.
primary_id / stat_cde
1 / R
1 / N
2 / R
2 / E
3 / N
3 / E
4 / N

What I want is:
primary_id / stat_cde
1 / R
2 / R
3 / E
4 / N

The 'R' record beats out an 'N' and 'E' record
The 'E' record beats out the 'N'.
The 'N' record wins if nothing else.

Any ideas on what function I should use to get this? I have thought about a case statement, nvl, and decode but that is only working for me if the different stat_cde's were all on one line. A distinct won't work because of the stat_cde.

I am at a block and not sure if it is my brain just fried. Any help would be appreciated!!!

Thanks in advance
Re: Multiple Rows Returned but Only Want One Based on stat_cde [message #319729 is a reply to message #319725] Mon, 12 May 2008 16:51 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try rank()
create table t_1 (primary_id number,  stat_cde varchar2(1))

insert into t_1 values(1, 'R');
insert into t_1 values(1, 'N');
insert into t_1 values(2, 'R');
insert into t_1 values(2, 'E');
insert into t_1 values(3, 'N');
insert into t_1 values(3, 'E');
insert into t_1 values(4, 'N');

commit;

select distinct decode (stat_cde, 'R', 1, 'E', 2, 'N', 3, 99), stat_cde from t_1 order by 1

DECODE(STAT_CDE,'R',1,'E',2,'N',3,99)	STAT_CDE
1	R
2	E
3	N



select primary_id, decode (stat_cde, 'R', 1, 'E', 2, 'N', 3, 99) cde_num, stat_cde,rank()
    over (partition by primary_id
         order by decode (stat_cde, 'R', 1, 'E', 2, 'N', 3, 99)) rnk
  from t_1;

PRIMARY_ID	CDE_NUM	STAT_CDE	RNK
1	1	R	1
1	3	N	2
2	1	R	1
2	2	E	2
3	2	E	1
3	3	N	2
4	3	N	1



SELECT primary_id, stat_cde
  FROM (SELECT primary_id, stat_cde,
               RANK () OVER (PARTITION BY primary_id 
                             ORDER BY DECODE(stat_cde,'R', 1,'E', 2,'N', 3,99)) rnk
          FROM t_1)
 WHERE rnk = 1;

PRIMARY_ID	STAT_CDE
1	R
2	R
3	E
4	N
Re: Multiple Rows Returned but Only Want One Based on stat_cde [message #319984 is a reply to message #319729] Tue, 13 May 2008 10:49 Go to previous messageGo to next message
anewhart21
Messages: 2
Registered: May 2008
Junior Member
This worked and I learned a new function, rnk. Thanks!!!!!
Re: Multiple Rows Returned but Only Want One Based on stat_cde [message #319985 is a reply to message #319984] Tue, 13 May 2008 10:51 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at the other ones: ROW_NUMBER, RANK and DENSE_RANK.

Regards
Michel

Previous Topic: What would happen if we use RETURN clause in Procedure?
Next Topic: null field and changing data question
Goto Forum:
  


Current Time: Thu Dec 08 12:23:26 CST 2016

Total time taken to generate the page: 0.11605 seconds