I'm quite new to SQL and I'm trying to filter the latest date record (DateTime column) for each unique ID present in the table.
Sample Data
There are 2 unique IDs (16512) and (76513).
| DateTime | ID | Notes |
|---|---|---|
| 2021-03-26T10:39:54.9770238 | 16512 | Still a work in Progress |
| 2021-04-29T12:46:12.8277807 | 16512 | Still working on it |
| 2021-03-21T10:39:54.9770238 | 76513 | Still a work in Progress |
| 2021-04-20T12:46:12.8277800 | 76513 | Still working on project |
Desired Result (Get last row of each ID based on DateTime column)
| DateTime | ID | Notes |
|---|---|---|
| 2021-04-29T12:46:12.8277807 | 16512 | Still working on it |
| 2021-04-20T12:46:12.8277800 | 76513 | Still working on project |
My Query
SELECT max(DateTime), ID
FROM Table1
GROUP BY DateTime, ID
Thanks in advance for you help.
My Query
SELECT max(DateTime), ID
FROM Table1
GROUP BY DateTime, ID
CodePudding user response：
SELECT max(DateTime), ID
FROM Table1
GROUP BY ID
CodePudding user response：
You can use row_number here
with d as (
select *, row_number() over(partition by Id order by DataTime desc)rn
)
select Datetime, Id, Notes
from d
where rn = 1;