Help on SQL Query

Hi,

I have a table called employee. I have fields like Emp.Nbr and login.date. I want to retrieve unique data of employee numbers. But I want unique with max(login.date) of an employee.

Ex: 111 7/19/2007

222 6/13/2007

111 7/25/2007

333 5/21/2007

222 7/12/2007

I want out put like

111 7/25/2007

222 7/12/2007

333 5/21/2007

How to write query for the above out put.

Kindly help.

Thanks

SELECT EMP.NBR,MAX(login.date)

FROM EMP INNER JOIN Login

on EMP.ID = Login.EmpId

Group by EMP.NBR

|||

select Nbr,max(LoginDate)

from emp

group by nbr

|||

Here it is,

Code Snippet

Create Table #emp (

[EmpId] Varchar(100) ,

[Date] DateTime

);

Insert Into #emp Values('111','7/19/2007');

Insert Into #emp Values('222','6/13/2007');

Insert Into #emp Values('111','7/25/2007');

Insert Into #emp Values('333','5/21/2007');

Insert Into #emp Values('222','7/12/2007');

Select EmpId, Max(Date) As date From #emp Group By EmpID

|||

hope this will suites your requirement: Here I have used Table Variable instead of Temporary Tables.

DECLARE @.Table TABLE ( Nbr int , LoginDate smalldatetime )

INSERT INTO @.Table (Nbr, LoginDate) VALUES(111, '7/19/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(222, '6/13/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(111, '7/25/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(333, '5/21/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(222, '7/12/2007')

SELECT

Nbr, max(LoginDate)

FROM @.Table

GROUP BY Nbr

Regards,

Prashanthi.