Query Help - most recent Record

[rkrause]

I have a table that has a field called jobcode In this field it could have the same job code for example MKRLF. There could be mulitple records in this table for MKRLF. THis table also has a field called EFFDate, and what i want to do is always get the jobcode field that is the most recent based off the effdate field

so if there was 5 MKRLF records in the table all with dates from 5 diffrent months and 1 was from 9-1-12 and all the others were from 2011, i want to get the most recent one. Im having trouble on how to do my query.

 

[pbaldy]

This technique should work:

http://www.baldyweb.com/LastValue.htm

If you're really in SQL Server, I'd probably make the first query a subquery in the join (FROM TableName INNER JOIN (SELECT...)...