Waller Waller - 1 month ago 19
SQL Question

Greatest-n per group MSSQL

I am using SQL-server 2008

My table looks like so:

max_period Employee ID Preferred Name
2016-10-19 00:00:00.000 16317 James Hello
2015-10-31 00:00:00.000 16317 Jimmy Hello


I am trying to only get the name with the greatest max_period, by Employee_ID

output would look like this:

max_period Employee ID Preferred Name
2016-10-19 00:00:00.000 16317 James Hello


Can someone help me solve this? It seems easy and first but is causing me a real headache.

Answer
;with cte
AS
(
select max_period  ,EmployeeID , PreferredName, ROW_NUMBER() OVER (PARTITION BY Employee_ID ORDER BY max_period DESC) as RN From Table1
)
SELECT * from cte WHERE RN = 1

You can do it with GROUP BY as well

select MAX(max_period), EmployeeID , PreferredName FROM Table1 GROUP BY EmployeeID , PreferredName 
Comments