sqldatetime-format

"End-of-day" select


I have a table, which contains some data with datetime in the below format (I'm using Dremio). For all columns in the table, I am trying to select only the last entry for a given day, e.g. for date 2023-07-18, row with time 10:30:23.537.

Date_and_time Parameter
2023-07-31 09:37:43.143 1
2023-07-18 10:30:23.537 2
2023-07-18 10:29:55.148 3
2023-07-10 09:43:32.187 4
2023-07-10 09:43:05.171 5
2023-07-10 09:42:36.498 6

I have tried the solution proposed in SQL: Select the last record for each day given datetime.

I removed the DATE parameter from the above example as it was giving errors.

SELECT t1.* FROM t1

INNER JOIN
(
    SELECT Date_and_time AS Date_time, MAX(Date_and_time) AS Date_time_end
    FROM t1
    GROUP BY Date_and_time
) t2
    ON t2.Date_time = t1.Date_and_time AND
       t2.Date_time_end = t1.Date_and_time
ORDER BY
    t1.Date_and_time;

Unfortunately, that has not given results as all rows were included in the result table.


Solution

  • This should do the trick

    SELECT t2.castdate,
           t1.parameter
    FROM table1 t1
    JOIN
      (SELECT CAST(Date_and_time AS DATE) AS 'castdate',
              max(Date_and_time) AS 'Date_and_time'
       FROM table1
       GROUP BY castdate) t2 ON t1.Date_and_time = t2.Date_and_time
    

    Query results in

    castdate parameter
    2023-07-31 1
    2023-07-18 2
    2023-07-10 4