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
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
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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
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
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.
If it has Yellow and Green records, Yellow should be returned.
If it has only Green records, one Green record should be returned.
>>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.
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.
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
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
Small tweak to flow01's code.
Try this:
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
/ 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?
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
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
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
What you order by is up to you but rnk is probably optional.
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
/ What you order by is up to you but rnk is probably optional.
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.
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.
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?
>>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:
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.
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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,'Yello
from yourtbl)
where rn = 1;
select id, nbr, code from
(select id, nbr, code,
row_number() over (partition by nbr order by decode(code,'Red',1,'Yello
from yourtbl)
where rn = 1;
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)?