sql - How to filter rows on Max date and Max Id -

this sql table

recordid      recordstate     time ----------------------------------- record1       failed          1:00 record1       passed          2:00  record2       passed          2:15  record3       failed          3:00  record4       passed          4:00 record4       failed          5:00 

i need select 'failed' records.

select recordid, max(recordstate) recordtable inner join recordstatetable b on a.recordid = b.recordid group recordid 

this pulls fail record problem here record4 value first passed , failed max(recordstate) record passed. somehow need modify max(time) cant figure how.

the expected result record3(failed),record4(failed)

instead of selecting max state can lead confusing results (since string) - can select max(time) each recordid , take records having failed state @ max time:

select t1.* (     select         recordid,         max(time) time     recordstatetable      group recordid ) t     inner join recordstatetable t1 on t1.recordid = t.recordid , t1.time = t.time t1.recordstate = 'failed'