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:
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,
CASE WHEN colour = 'Red' THEN 'High'
WHEN colour = 'Yellow' THEN 'Med'
WHEN colour = 'Green' THEN 'Low'
ELSE 'x' END Priority
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: