SQLTeam.com | Weblogs | Forums

Query max row from groups

For only certain records I need to query the most recent image01 field (i.e. MAX(DATE)) record. I'm close but not quite there. Your help is greatly appreciated. Thank you.

select employee_id, image01,
row_number() over(partition by employee_id order by date desc) as rn
from table1
where employee_id in (
'N1996-005001410',
'ECSO16MNI052715',
'ECSO18MNI042307',
'ECSO07MNI006432',
'ECSO15MNI012982',
'ECSO11MNI019296',
'ECSO00MNI011950')

this should work
select Employee_ID, Image01
from (
select employee_id, image01,
row_number() over(partition by employee_id order by date desc) as rn
from table1
where employee_id in (
'N1996-005001410',
'ECSO16MNI052715',
'ECSO18MNI042307',
'ECSO07MNI006432',
'ECSO15MNI012982',
'ECSO11MNI019296',
'ECSO00MNI011950') )v
where rn = 1