MSSQL Query Help

[gmrstudios]

I have a database with the following fields:

DbKey, EmpNum, EmpName, Department, WorkStarted, WorkEnded.

I run into issues when I try to limit my query to the last entry into the database for a particular employee from a particular department.

SELECT DbKey, EmpNum, EmpName, Department, WorkStarted, WorkEnded FROM TimeTable WHERE Department = 'FRONTOFFICE'

will produce every single entry for every employee in the front office.

This is the result I get back:

[INDENT]165005FIGUEROA, REMIEFRONTOFFICEFeb 16 2009 2:37PM
165004HODGE, KELLYFRONTOFFICEFeb 16 2009 2:33PM
165003HODGE, KELLYFRONTOFFICEFeb 16 2009 2:09PMFeb 16 2009 2:29PM
165002ITURIO, CLAUDIAFRONTOFFICEFeb 16 2009 2:32PM
164991ASHLEY, LISAFRONTOFFICEFeb 16 2009 1:57PM
164986JOHNSON, LISAFRONTOFFICEFeb 16 2009 1:43PM[/INDENT]

Is there a way to limit it to only the last entry for each employee? I would only like the query to show the last entry into the database from Kelly Hodge, or any EmpName produced by the query. I tried using varations of max() but had no luck. Thanks in advance for your help!

[gmrstudios]

This is what I was able to come up. If you think this can be optimized feel free to share.

SELECT t.DbKey
     , t.EmpNum
     , t.EmpName
     , t.Department
     , t.WorkStarted
     , t.WorkEnded 
  FROM TimeTable AS t
INNER
  JOIN ( SELECT EmpNum
              , MAX(WorkStarted) AS maxdate
           FROM TimeTable
          WHERE Department = 'FRONTOFFICE'
         GROUP
             BY EmpNum ) AS m
    ON m.EmpNum = t.EmpNum
   AND m.maxdate = t.WorkStarted

[tomhath]

That should work fine. Another way to do the same thing is with NOT EXISTS, don't know if it would be any more efficient though:

SELECT t.DbKey
     , t.EmpNum
     , t.EmpName
     , t.Department
     , t.WorkStarted
     , t.WorkEnded 
  FROM TimeTable AS t
  WHERE NOT EXISTS (
    SELECT 1
    FROM TimeTable AS m
    WHERE m.EmpNum = t.EmpNum
      AND m.WorkStarted > t.WorkStarted
    )