Pick Latest training date query

[niceguy606]

Employee_ID SOP_ID SOPCode Training_Date Rev
96 3 TP0 03/05/2003 0
96 3 TP0 04/05/2004 0
96 6 TP0 04/03/2005 1
96 7 TP0 NULL 2
96 83 TM0 04/03/2005 0

Can anyone help me with this query
how to get a output like this

96 7 TP0 NULL 2
96 83 TM0 04/03/2005 0

I need to get the latest training date for the max rev number for the same SOPCode.


pls thanks

 

[bborissov]

SELECT * FROM MyTable WHERE Sop_ID IN (SELECT MAX(Sop_Id) FROM MyTable AS MyTab2 WHERE  MyTab2.Employee_ID = MyTable.Employee_ID)

not tested

Borislav Borissov

 

[rac2]

I would guess that the training date for the max rev number is the same thing as the "latest training date". That is, the max rev number row also has the latest training date. If so then-

SELECT * FROM MyTrainingTable a
JOIN (
      SELECT SOPCode, MAX(Rev) AS "HiRev"
      FROM MyTrainingTable
      GROUP BY SOPCode
      ) b ON b.SOPCode = a.SOPCode
          AND b.HiRev = a.Rev