Rohit Rohit - 17 days ago 6
SQL Question

using case to select multiple conditions

This is my SQL table

RecordID RecordState Time
-----------------------------------
record1 failed 9:10
record2 failed 9:20
record2 passed 9:30


this is my query.

select RecordID, max(RecordState)
from RecordTable a
group by RecordID


For the record
record2
the value was first failed and then passed so max(recordstate) for this record is passed

RecordID RecordState Time
-----------------------------------
record1 failed 9:10
record2 passed 9:30


Now here is a different scenario

RecordID RecordState Time
-----------------------------------
record1 failed 9:10
record2 failed 9:20
record2 passed 9:30
record2 failed 9:40


at 9:40 the record failed again so my final output should be

RecordID RecordState Time
-----------------------------------
record1 failed 9:10
record2 failed 9:40


however since I am using Max(RecordState) it will always return 'passed' I need to somehow also check for the latest time and display fail in this case (9:40).

Answer

Use ROW_NUMBER(), and order by Time DESC

SQL DEMO

SELECT RecordID, RecordState, Time
FROM ( SELECT RecordID, RecordState, Time,
              ROW_NUMBER() over (PARTITION BY RecordID      
                                 ORDER BY Time DESC) as rn
       FROM YourTable
      ) T
WHERE T.rn =1

OUTPUT

enter image description here

Comments