英文:
SQL: "End-of-day" select
问题
我有一个表格，其中包含以下格式的一些日期时间数据（我正在使用Dremio）。对于表中的所有列，我试图仅选择给定日期的最后一条记录，例如日期2023-07-18，时间为10:30:23.537。
我尝试了SQL：根据日期时间选择最后一条记录中提出的解决方案。
我从上面的示例中移除了日期参数，因为它导致了错误。
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;
不幸的是，这没有产生结果，因为结果表中包括了所有行。
英文:
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.
答案1
得分: 1
这应该可以解决问题
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
查询结果如下：
| castdate | parameter |
|---|---|
| 2023-07-31 | 1 |
| 2023-07-18 | 2 |
| 2023-07-10 | 4 |
英文:
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 |
答案2
得分: 0
这可能对你有帮助：
select * from table where date(Date_and_time)='2023-07-18' order by Date_and_time desc limit 1
英文:
This might help you :
select * from table where date(Date_and_time)='2023-07-18' order by Date_and_time desc limit 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论