Groupby - 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

Groupby

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2011-11-30 : 16:49:23

Hi,
I have a table that has the Employee #, Seq # and process_date. I want to get the process_date for the maximum seqence number.

Sample Data:

Emp_number seq process_dt
2011053 1 2010-02-01 00:00:00.000
2011053 2 2010-05-07 00:00:00.000
2011151 1 2009-07-31 00:00:00.000
2011151 2 2009-08-18 00:00:00.000
2011151 3 2009-09-24 00:00:00.000
2011153 1 2009-09-24 00:00:00.000

I want the ouput to be
Emp_number seq process_dt
2011053 2 2010-05-07 00:00:00.000
2011151 3 2009-09-24 00:00:00.000
2011153 1 2009-09-24 00:00:00.000

In the below query i can get only the records with max(seq) number.

SELECT Emp_number, MAX(seq)
FROM Table1
where process_dt IS NOT NULL
GROUP BY Emp_number

If i want to add the process_dt to the output, should i store the result to a table variable and then do a join to the EmpTbl to get
the process_dt ?. Is there any other way in the group by ?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-11-30 : 16:55:29

If you are using SQL Server 2005 or higher,

select Emp_number,seq,process_dt
from
(
select row_number() over(partition by Emp_number order by seq desc) as rn, * from Table1
) t
where t.rn = 1

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2011-11-30 : 17:05:48

Thank you !. This works perfectly.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-11-30 : 17:14:02

quote:
Originally posted by pvccaz

Thank you !. This works perfectly.

np. glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources