views:

29

answers:

1

In the table 'Emplyoee' there are two fields-JoinDate and EmployeeName.

All Data contains in Emplyoee table is as follows:

JoinDate      | EmployeeName
------------------------
02-12-2009   Vijay

03-12-2009   Binoy

03-12-2009   Rahul

My select query is as follows:

SELECT DISTINCT JoinDate,EmployeeName FROM Emplyoee

I got the Result as follows:

JoinDate   | EmployeeName
------------------------
02-12-2009   Vijay
03-12-2009   Binoy
03-12-2009   Rahul

But i need the result as follows:

JoinDate   | EmployeeName
------------------------
02-12-2009   Vijay
03-12-2009   Binoy(first employee joined on this date)
A: 

This will select the first employee alphabetically that joined on each date:

 SELECT DISTINCT mydates.JoinDate, 
    (SELECT TOP 1 EmployeeName FROM Employee e2 WHERE e2.JoinDate=mydates.JoinDate ORDER BY EmployeeName)
 FROM Employee mydates
richardtallent

related questions