SQLite 中 WHERE 子句中的聚合函数 [英] Aggregate functions in WHERE clause in SQLite

问题描述

简而言之，我有一个表格，其中包含一列时间戳.我想获取具有最新(即最大值)时间戳的行.目前我正在这样做:

Simply put, I have a table with, among other things, a column for timestamps. I want to get the row with the most recent (i.e. greatest value) timestamp. Currently I'm doing this:

SELECT * FROM table ORDER BY timestamp DESC LIMIT 1

但我更愿意做这样的事情:

But I'd much rather do something like this:

SELECT * FROM table WHERE timestamp=max(timestamp)

然而，SQLite 拒绝了这个查询:

However, SQLite rejects this query:

SQL error: misuse of aggregate function max()

文档证实了这种行为(页面底部):

The documentation confirms this behavior (bottom of page):

聚合函数只能在 SELECT 语句中使用.

Aggregate functions may only be used in a SELECT statement.

我的问题是:是否可以编写一个查询来获取具有最大时间戳的行，而无需对选择进行排序并将返回的行数限制为 1?这似乎应该是可能的，但我想我的 SQL-fu 不符合要求.

My question is: is it possible to write a query to get the row with the greatest timestamp without ordering the select and limiting the number of returned rows to 1? This seems like it should be possible, but I guess my SQL-fu isn't up to snuff.

推荐答案

SELECT * from foo where timestamp = (select max(timestamp) from foo)

或者，如果 SQLite 坚持将子选择视为集合，

or, if SQLite insists on treating subselects as sets,

SELECT * from foo where timestamp in (select max(timestamp) from foo)

这篇关于SQLite 中 WHERE 子句中的聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！