sqlsortinggroup

Group by max value for each day

ID Value Date
1 10 2023-10-12 16:10:59.457
1 20 2023-10-12 14:20:54.997
1 30 2023-10-13 10:30:59.457
1 70 2023-10-13 10:20:54.997
2 10 2023-10-12 18:00:59.457
2 20 2023-10-13 14:00:54.997

I have a table with and ID, some values and a Date fiel and I want to group than by ID and day but just taking the max date value of each ID. My DB is SQL Server.

ID Value Date
1 10 2023-10-12 16:10:59.457
1 30 2023-10-13 10:30:59.457
2 10 2023-10-12 18:00:59.457
2 20 2023-10-13 14:00:54.997
Solution

  • Use Windowing concept to solve this problem.

    SELECT id,
   value,
   date,
   ROW_NUMBER(date) OVER
     (PARTITION BY id,TO_DATE(date) ORDER BY date DESC)
     AS row_number
  FROM YOUR_TABLE 
WHERE row_number = 1

    There would be additional column row_number which developer need to remove or ignore. :)