'Return ID based on maximum date for each organisation SQL Sever 2012 [duplicate]
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|