SQL get single row based on multiple condition after group by

Question

Need help with creating query for below case :

Suppose I have a Table with following records

Name      Date        Time   Category CategoryKey
John      10/20/2012  10:00  Low       2
Sam       10/20/2012  10:00  High      4
Harry     10/20/2012  10:00  Medium    1
Michael   10/20/2012  10:00  Grey      3
Rob       10/22/2013  11:00  Low       2
Marry     10/23/2014  12:00  Low       2
Richard   10/23/2014  12:00  Grey      3
Jack      10/24/2015  1:30   High      4

Then If there are multiple Names for same date and time then force select only one record based on following logic and stop when any 1 of the following condition is met.

If Category is Medium then take name 
Else If Category is High then take name
Else If Category is Low then take name
Else If Category is Grey then take name

So that the Final result will be

Name      Date        Time   Category CategoryKey

Harry     10/20/2012  10:00  Medium    1
Rob       10/22/2013  11:00  Low       2
Marry     10/23/2014  12:00  Low       2
Jack      10/24/2015  1:30   High      4

Odd, all rows for a date have the same time. – jarlh Feb 17 '20 at 14:33 — jarlh, Feb 17 '20 at 14:33

score 2 · Accepted Answer · answered Feb 17 '20 at 14:32

The simplest method is row_number():

select t.*
from (select t.*,
             row_number() over (partition by date, time
                                order by (case category when 'Medium' then 1 when 'High' then 2 when 'Low' then 3 when 'Grey' then 4 else 5 end)
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

It can be convenient to use string functions here:

             row_number() over (partition by date, time
                                order by charindex(category, 'Medium,High,Low,Grey')
                               ) as seqnum

This works for your case, but you need to be a little careful that all values are included and that none "contain" another value.

SQL get single row based on multiple condition after group by

1 Answers1