0

I am trying to return a single ID for each organisation based on the maximum date and am a bit lost.

I have created a dummy dataset below to replicate mine.

ID OrganisationID record date
1 200 21/01/2022
2 100 28/01/2022
3 200 17/01/2021
4 120 14/08/2019
5 100 21/05/2019
6 150 12/04/2021
7 100 07/02/2022
8 200 21/03/2022
9 150 16/09/2021
10 100 01/02/2022

I would like to return the ID of the entry with the highest date for each Organisation, results would looke like

ID OrgsansiationID recordDate
8 200 21/03/2022
7 100 07/02/2022
9 150 16/09/2021
4 120 14/08/2019

Any help you could give me would be greatly appreciated, thanks in advance

Jake
  • 3
  • 1

1 Answers1

-1

This can be done with a top 1 with ties

select top 1 with ties
       o.id,
       o.organisationid,
       o.record_date
from   organisation o
order by row_number() over (partition by o.organisationid order by o.record_date desc)

See this DBFiddle

GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • I'd hazard a guess that it's because this method is already in the [linked duplicate](https://stackoverflow.com/a/48412942/2029983), @GuidoG . That or this question is asked almost as often as "How do I split a delimited string into rows" and they feel that we don't need *another* answer telling using the same thing. Just throwing ideas out there. – Thom A Feb 28 '22 at 16:22