Simple GROUP BY problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple GROUP BY problem

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-05-14 : 13:34:49

Hi All, I'm using SSMS 2008, and working with a Table that's similar to this:
[CODE]
EMP--NAME--------START_DATE---END_DATE
A001 JOEL SMITH 21-JAN-2011 14-JUN-2011
A001 MIKE ADAMS 02-DEC-2011 11-FEB-2012
A001 KIRA FAULK 08-FEB-2012 29-FEB-2012
A002 ASHA HAMDI 21-JAN-2011 14-JUN-2011
A002 MIKE OLSEN 02-DEC-2011 11-FEB-2012
A002 FRANK HALL 29-APR-2012 NULL
[/CODE]

The table is an "Employee" Table that has an Employee ID which is re-used. I need my query to output the latest instance of the employee ID (based on the most RECENT start date). This is how the result set SHOULD appear:

[CODE]
EMP--NAME--------START_DATE---END_DATE
A001 KIRA FAULK 08-FEB-2012 29-FEB-2012
A002 FRANK HALL 29-APR-2012 NULL
[/CODE]

Below is my query. It still outputs duplicate employee IDs because the employee names are different.
[CODE]
SELECT EMP, NAME, MAX(START_DATE), END_DATE
FROM EMPLOYEE
GROUP BY EMP, NAME, END_DATE
[/CODE]
How can this be tweaked so that it outputs only one distinct employee number per row? THANKS GUYS!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-14 : 13:50:42

Try this

select *
from
(
select tt.Emp,tt.Name,tt.StartDate,tt.EndDate
,[rown] = ROW_NUMBER() over(partition by emp order by tt.startdate desc)
FROM yourTable tt
) t1

where t1.rown = 1

Jim

Everyday I learn something that somebody else already knew

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-05-14 : 14:26:24

WORKS!!!!!!!!!!!!!!!!!!!!!!
THANKS !!!!!!!!!!!!!!!!!!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources