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.
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 |