How to get last record of each employee? 3

[Katya85S]

How can I select employees' last title (a record of the last promotion) from SQL server database? As employee gets promoted new record is added to the database with new EmpNum (the number gets increased).
I thought max function should do the trick, but my query brings all records.
SELECT max(tblEmpBase.EmpNum), tblEmpStatus .Title
FROM tblEmpBase INNER JOIN tblEmpStatus ON tblEmpBase.SSN= tblEmpStatus.SSN
Thank you all in advance.

 

[gmmastros]

Select A.MaxEmpNum As EmpNum,
       tblEmpStatus.Title
From   (
       Select tblEmpBase.SSN,
              Max(tblEmpBase.EmpNum) As MaxEmpNum
       From   tblEmpBase
       Group By tblEmpBase.SSN
       ) As A
       Inner Join tblEmpStatus
         On A.SSN = tblEmpStatus.SSN

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

It is not clear. Didi you want the last promotion for each employee or you want the last promotion in the firm?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Katya85S]

Thank you guys for replay.
Yes, i needed the last promotion for each employee. Sorry for the confusion.

Gmmastros, your query did the job. Many many thanks!