Avatar of smalig
smalig
 asked on

Oracle SQL

Experts -
I have a table with 3 attributes -- ID, NBR and CODE.
values in the table will be

ID                            NBR                          CODE
43247                     46064                       Red
52395                     52268                      Green
52760                     52268                      Green
49025                     52852                      Green
49025                     52852                      Red
49025                     52852                      Yellow
51681                     72523                      Green
45202                     131179                     Yellow
13127                     440587                       Yellow
13128                    440587                     Green

Can I have a query where the output is chosen depending on the code for the unique combination of ID and NBR
the order of CODE is Red, Yellow and Green

ex: for NBR of 52852  I have all 3 CODE's i.e. Red, Yellow and Green. the output should be of the record Red only
Similarly for 440587 I have 2 CODE's i.e. Yellow and Green. the output should be of the record Yellow only

If there are multiple Red's, Yellow's or Green's only one record can be shown (not all is needed in the output)
Thanks for your help
Oracle Database

Avatar of undefined
Last Comment
awking00

8/22/2022 - Mon
SOLUTION
flow01

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

flow01 is on the right track and the final solution will likely look very similar but...

Please post expected results based on your sample data.

You have two different ID's for NBR 440587, so what is returned?

What it there is a Red and Green but no yellow (and red yellow no green)?
smalig

ASKER
from flow1's solution output is --
13127      440587      Yellow
13128      440587      Green
43247      46064      Red
45202      131179      Yellow
49025      52852      Red
51681      72523      Green
52395      52268      Green
52760      52268      Green

But, the values are in the table, in the select 'with t1 as' why am I using the values for ID, NBR and CODE
smalig

ASKER
for 440587 'Yellow' should be returned as the order if Red, Yellow and Green. It is like if 440587 has records with Red, Yellow and Green then Red should be returned.
If it has Yellow and Green records, Yellow should be returned.
If it has only Green records, one Green record should be returned.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

>>from flow1's solution output is --

Yes, I can get that by running his code.  It is his output that prompted my questions.

What I want is the output you expect from the data you provided.
smalig

ASKER
Oh sorry. I expect the output of --

ID                            NBR                          CODE
43247                     46064                       Red
 52395                     52268                      Green -OR-  52760                     52268                      Green (Can be one of them either by max NBR or ID)
49025                     52852                      Red
51681                     72523                      Green
 45202                     131179                     Yellow
13127                     440587                       Yellow
slightwv (䄆 Netminder)

Small tweak to flow01's code.

Try this:
with t1
as
(
select 43247  ID ,  46064  NBR, 'Red'   CODE from DUAL UNION all
select 52395     ,  52268    ,'Green'        from DUAL UNION all
select 52760     ,  52268    ,'Green'        from DUAL UNION all
select 49025     ,  52852    ,'Green'        from DUAL UNION all
select 49025     ,  52852    ,'Red'          from DUAL UNION all
select 49025     ,  52852    ,'Yellow'       from DUAL UNION all
select 51681     ,  72523    ,'Green'        from DUAL UNION all
select 45202     ,  131179   ,'Yellow'       from DUAL UNION all
select 13127     ,  440587   ,'Yellow'       from DUAL UNION all
select 13128     ,  440587   ,'Green'        from DUAL
)
,
s1
as
(
select id, nbr, code, row_number() over (partition by nbr order by CASE CODE
                                                                 WHEN 'Red' THEN 1
                                                                 WHEN 'Yellow' THEN 2
                                                                 WHEN 'Green' THEN 3
                                                                 ELSE
                                                                   4
                                                                 END   ) rnk
 from t1
)
select id, nbr,  code
from s1
where rnk = 1
order by id, nbr,  rnk
/ 

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
smalig

ASKER
if I want to select from a table how will s1 look as? I mean I should not have to plug in the values into the query right?
smalig

ASKER
this piece of code --
with t1
as
(
select 43247  ID ,  46064  NBR, 'Red'   CODE from DUAL UNION all
select 52395     ,  52268    ,'Green'        from DUAL UNION all
select 52760     ,  52268    ,'Green'        from DUAL UNION all
select 49025     ,  52852    ,'Green'        from DUAL UNION all
select 49025     ,  52852    ,'Red'          from DUAL UNION all
select 49025     ,  52852    ,'Yellow'       from DUAL UNION all
select 51681     ,  72523    ,'Green'        from DUAL UNION all
select 45202     ,  131179   ,'Yellow'       from DUAL UNION all
select 13127     ,  440587   ,'Yellow'       from DUAL UNION all
select 13128     ,  440587   ,'Green'        from DUAL
)
,

I want to select the values from a table. not plug values into the query
slightwv (䄆 Netminder)

The WITH code (commonly called CTE) is just an easy way to dummy up sample data.

It defines a table called t1 dynamically.

It is the last select itself that is important.


If your table is called: unicorns_and_rainbows, just use
select id, nbr,  code
from
(
select id, nbr, code, row_number() over (partition by nbr order by CASE CODE
                                                                 WHEN 'Red' THEN 1
                                                                 WHEN 'Yellow' THEN 2
                                                                 WHEN 'Green' THEN 3
                                                                 ELSE
                                                                   4
                                                                 END   ) rnk
from unicorns_and_rainbows
)
where rnk = 1
order by id, nbr,  rnk
/ 

Open in new window


What you order by is up to you but rnk is probably optional.
Your help has saved me hundreds of hours of internet surfing.
fblack61
smalig

ASKER
sorry slightw. I have to plug in the attribute names. got it. let me give few more tests. looks like I am getting the output.
smalig

ASKER
I was testing the solution multiple times. I am finding I am missing one record where NBR of 52268 associated with ID 52760 does not show up. I can see only NBR of 52268 associated with 52395 in the output.
smalig

ASKER
sorry. I mentioned I need to see one of them in my earlier post. but, if I want to see both, is there any modification to the query?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

>>I am missing one record where NBR of 52268 associated with ID 52760 does not show up. I can see only NBR of 52268 associated with 52395 in the output.

That was from your expected results:
52395                     52268                      Green -OR-  52760                     52268                      Green (Can be one of them either by max NBR or ID)

The "OR" implies one or the other, not both.

The post from flow01 shows BOTH.

Which is correct?

Please provide expected results from your posted data.  Feel free to add to the actual data and update your expected results as you see fit.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
awking00

Slightly different approach - Note adding order by id desc produces the row with the highest id in the case of ties. Since you indicated it could be either, you could also order by id asc.
select id, nbr, code from
(select id, nbr, code,
row_number() over (partition by nbr order by decode(code,'Red',1,'Yellow',2,'Green',3), id desc) rn
from yourtbl)
where rn = 1;