SQL 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 of all the same SOPCodes even when the rev is different or it could be null.

this is the original thread for it but couldnt get the answer.

pls thanks

http://www.tek-tips.com/viewthread.cfm?qid=1114156&page=1

 

[DotNetGnat]

Try this:

SELECT A.Employee_ID,A.Sop_ID,
       A.SOPCode, A.Training_Date,
       A.Rev
FROM mytable A WHERE
A.Training_Date IN (
SELECT TOP 1 Training_Date FROM mytable B
WHERE A.SOPCode=B.SopCode
ORDER BY Training_Date DESC
)

-DNG

 

[niceguy606]

thanks for ur help. didnt work.
I tried that query

I get this result

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

Even thought SOP_ID are different for 3 and 6 the r same. They r revised.

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