SQL query help

[DrKeith]

I am trying to write a SQL query to return a row with the most recent date. There are many other columns, but this is a sample. I need to group by the ID field, and want to return the row with the latest contact date for each ID. For the sample below, I would need to return rows 2 and 6.
I have not had luck with using a combination of DISTINCTROW and Max(ContactDate). Any suggestions?


ID Prkey RelDate Var1 ContactDate
1 1 6/5/90 2 10-Feb-91
1 2 6/5/90 1 17-Aug-99
2 3 1 10-Feb-95
2 4 1 10-Aug-97
2 5 1 10-Feb-99
2 6 1 17-Aug-05

 

[r937]

select ID
     , Prkey    
     , RelDate     
     , Var1   
     , ContactDate
  from daTable as T
 where ContactDate =
       ( select max(ContactDate)
           from daTable
          where ID = T.ID )

r937.com | rudy.ca

 

[PHV]

Another way:
SELECT A.ID, A.Prkey, A.RelDate, A.Var1, A.ContactDate
FROM yourTable AS A INNER JOIN (
SELECT ID, Max(ContactDate) AS LastDate FROM yourTable GROUP BY ID
) AS B ON A.ID = B.ID AND A.ContactDate = B.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886