This is the table:
Order_ID|Priority|Status
A |3 |Normal
A |4 |Urgent
B |6 |Urgent
B |7 |Normal
C |8 |Normal
C |9 |Urgent
How do I select the Order_ID and Status where a row's priority is higher for that ID? For example, in this case, the output of the query given the above data should be:
A - Urgent
B - Normal
C - Urgent
One way to do it is like this:
select order_id || ' - ' || status
from (
select order_id, priority, status,
rank() over (partition by order_id order by priority desc) ranking
from table
)
where ranking = 1;
The record with the highest priority -->> there is no record with a higher priority
SELECT *
FROM order_status o
WHERE NOT EXISTS(
SELECT *
FROM order_status x
WHERE x.order_id = o.order_id
AND x.priority > o.priority
)
;
Another way to do this with SQL would be to simply get the MAX(Priority) for each Order_ID and join back to that table using a SUBSELECT:
SELECT x.ORDER_ID + ' - ' + x.Status AS [Output],
x.Priority ,
x.Status
FROM ( SELECT Order_ID AS Order_ID ,
MAX(Priority) AS [Priority]
FROM @your_table
GROUP BY ORDER_ID
) t
JOIN @your_table x ON x.Order_ID = t.Order_ID
AND x.[Priority] = t.[Priority];
Where @your_table is whatever the name of your table is.
This is the output of the query:
Output Priority Status
A - Urgent 4 Urgent
B - Normal 7 Normal
C - Urgent 9 Urgent
select Order_ID || ' - ' || (
select top 1 Status
from priorities as p2
where p1.Order_ID = p2.Order_ID
order by p2.Priority desc)
from priorities as p1
group by Order_ID
order by max(Priority)
Returns
A - Urgent
B - Normal
C - Urgent
