Oracle SQL help

Forums › IT Pro and developers › Oracle SQL help

nicnzl

172 posts

Master Geek

#130767 27-Sep-2013 09:44

Hi,

I'm a bit stuck with finding the right sql (ORACLE), but I think I'm just over complicating things now and can't see the obvious solution.

I'll try make it as simple as possible.

I have a table with duplicate ids, but different colours assigned to each id:

1 Green
1 Yellow
2 Red
2 Yellow
3 Green

I want select only one of each id, with the highest ranked colour of each id.
We'll say Red is the highest, then yellow, then green.
I also want to turn the colours into a priority instead of colour,
e.g.

SELECT id,
CASE WHEN colour = 'Red' THEN 'High'
WHEN colour = 'Yellow' THEN 'Med'
WHEN colour = 'Green' THEN 'Low'
ELSE 'x' END Priority
FROM table

This will obviously return a result for every row in the original table, so how would I go about bringing back one row per id and getting the following results:

1 Med
2 High
3 Low

Thanks,
Nic

Inphinity

2780 posts

Uber Geek

#903559 27-Sep-2013 09:52

So you want the result with the highest numeric value for each colour name?

There are more efficient ways to do it, but probably the simplest that comes to mind would be

select id, 'High' from table_name where colour = 'Red'
union
select id, 'Med' from table_name where colour = 'Yellow' and id not in (select id from table_name where colour = 'Red')
union
select id, 'Low' from table_name where colour = 'Green' and id not in (select id form table_name where colour in ('Red','Yellow'))

dolsen

1476 posts

Uber Geek

Trusted

Lifetime subscriber

#903582 27-Sep-2013 10:08

Not sure how abstract your example is from your real question. How many of these "colours" are there? Will there be any changes to the colours / ranking of them in future?

Would it be worth having a lookup table of colour / priority / order which you could join against and use that to find the max record? That way, not only is it easier, if you add / change any in future you don't have to modify the sql as you would with the case statement.

nicnzl

172 posts

Master Geek

#903599 27-Sep-2013 10:46

I'm working with a few million rows but there are only 3 different "colours", and there will be no additions or change of ranking in the future.

A lookup table is not an option I'm afraid.

The colours are assigned 1 to 1 on a secondary id. The primary id has many secondary ids and therefore many "colours".
i.e

Primary_Id Second_Id Colour
1 111 Green
1 222 Yellow
2 333 Red
2 444 Yellow
3 555 Green

But we have to work at the primary id level and only return one row per id.

I did consider unions and it works at a very basic level, but not so well in practice as things get more complicated.

nzkc

1572 posts

Uber Geek

#903616 27-Sep-2013 11:22

<soapbox>
This is why database normalisation is important! You could add rankings to the Colour to allow this. Your query would become a simple SELECT MIN/MAX with GROUP BY
</soapbox>

Afraid I'm not an Oracle person, but I can give you a solution that works on SQL Server. Hopefully you can adapt it for Oracle:

CREATE TABLE #Values(
ID INT,
Colour NVARCHAR(10)
)

INSERT INTO #Values
VALUES(1, 'Green'), (1, 'Yellow'), (2, 'Red'), (2, 'Yellow'), (3, 'Green')

SELECT * FROM #Values

SELECT
ID
,CASE MIN(ColourRank)
    WHEN 1 THEN 'Red'
    WHEN 2 THEN 'Yellow'
    WHEN 3 THEN 'Green'
    ELSE 'Unknown'
END AS Colour
FROM (
SELECT
    ID
    ,Colour
    ,CASE Colour
      WHEN 'Red' THEN 1
      WHEN 'Yellow' THEN 2
      WHEN 'Green' THEN 3
      ELSE 4
    END AS ColourRank
FROM
    #Values
) AS CalcValues
GROUP BY
ID

Its a bit hacky but would need to see your schema to give you anything better.

dolsen

1476 posts

Uber Geek

Trusted

Lifetime subscriber

#903618 27-Sep-2013 11:29

Looking back at the first request, something simple like this then.

SELECT Primary_Id,
DECODE (
MAX (
CASE cl
WHEN 'green' THEN 1
WHEN 'yellow' THEN 2
WHEN 'red' THEN 3
END),
3, 'High',
2, 'Med',
1, 'Low') as result
FROM table
GROUP BY Primary_Id
order by Primary_Id

Note the mixture of decode and case for a bit of variety :-)

Haven't done anything with your 'x' from your first case statement.

Edit - didn't see above reply before adding mine.

TwoSeven

1624 posts

Uber Geek

Subscriber

#903630 27-Sep-2013 11:54

One can do the math to work out the solution as it is basic set theory. The problem that one may be encountering is that the output (solution) needs to be an ordered set from which one then selects the most significant item. The issue is that in the data, one is missing the bit of information that allows the set to be ordered.

This can be fixed by adding the missing information (meta-data) in the SELECT statement which is what some of the above answers are doing.

Another way is to look at the way the index would be built and it gives an idea of the issue from a different perspective. If one has enough data one could build a clustered index; walking the index would lead straight to the result required. Since a bit of data is missing, one can only use a non-clustered index, which means that the result of walking the index would return a set of unordered items. The requirement is to then order that set and select the most significant item.

The metadata for filtering the result into an ordered set is a human interpretation of the priority value of red, green etc.

I know this post isn't really a solution to the problem, I would more trying give some understanding to the problem.

Software Engineer
(the practice of real science, engineering and management)
A.I. (Automation rebranded)
Gender Neutral
(a person who believes in equality and who does not believe in/use stereotypes. Examples such as gender, binary, nonbinary, male/female etc.)

...they/their/them...

nicnzl

172 posts

Master Geek

#903796 27-Sep-2013 15:08

Thanks everyone,

Worked everyone's answers into a solution.

But of course, fix one problem and another occurs. Should be able to fix this one myself though.

Thanks again.