Select top 1 from each group -- sql server data mining

Peter
9/28/2003 11:31:14 PM

I have the following data

RecordID EmpID LastName FirstName SomeDate

1 1234 Smith Bob 01/01/2003
2 1234 Smith Bob 01/02/2003
3 1234 Smith Bob 01/03/2003
4 1234 Smith Bob 01/04/2003
5 5678 Jones Joe 11/11/2003
6 5678 Jones Joe 11/12/2003
7 5678 Jones Joe 11/13/2003
8 5678 Jones Joe 11/14/2003

How would I select top one record based on SomeDate from each group.

what would be the SQL for that?

--=20
Thanks
-------------------

Vishal Parkar
9/29/2003 10:45:07 AM

Try:

select a.*
from table1 a join
(select empid, max(somedate) somedate
from table1 group by empid) b
on
a.empid = b.empid and a.somedate=b.somedate

- Vishal

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server data mining

sql server data mining : Select top 1 from each group