Get Max Date for Group and it's Associated Record ID

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63519 More actions · Answer 1

For a newb, nice job posting!

This should do it... you might want to play with ROW_NUMBER() for a while... super handy once you figure out how it works.

SELECT y.Class

, y.ID

, y.ClassDate

, y.rn

FROM

(SELECT Class,

ID,

ClassDate,

ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Class, ClassDate DESC) AS rn

FROM

(

SELECT 1 AS ID, 'Class A' AS Class, '1/1/16' AS ClassDate

UNION ALL

SELECT 2 , 'Class A' , '2/1/16'

UNION ALL

SELECT 3 , 'Class A' , '3/1/16'

UNION ALL

SELECT 4 , 'Class B' , '4/1/16'

UNION ALL

SELECT 5 , 'Class C' , '5/1/16'

) x ) y

WHERE y.rn = 1;

ptownbro SSCrazy Points: 2011 More actions · Answer 2

Wow... so.. um... yeah. I don't understand how that works, but it works!

Thanks a lot for your help!

I'll have to study this one.

Didn't know you could do two "FROM" statements

ptownbro SSCrazy Points: 2011 More actions · Answer 3

Oh wait duh. I see it now. It's two nested sub-queries. Anyway... still great stuff and will have to study the ROW_NUMBER() function.

Thank you! Thank you!