Aggregate functions in WHERE clause in SQLite

Question

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)

However, SQLite rejects this query:

SQL error: misuse of aggregate function max()

The documentation confirms this behavior (bottom of page):

Aggregate functions may only be used in a SELECT statement.

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.

Solution 1

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

or, if SQLite insists on treating subselects as sets,

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

Solution 2

There are many ways to skin a cat.

If you have an Identity Column that has an auto-increment functionality, a faster query would result if you return the last record by ID, due to the indexing of the column, unless of course you wish to put an index on the timestamp column.

SELECT * FROM TABLE ORDER BY ID DESC LIMIT 1

Solution 3

I think I've answered this question 5 times in the past week now, but I'm too tired to find a link to one of those right now, so here it is again...

SELECT
     *
FROM
     table T1
LEFT OUTER JOIN table T2 ON
     T2.timestamp > T1.timestamp
WHERE
     T2.timestamp IS NULL

You're basically looking for the row where no other row matches that is later than it.

NOTE: As pointed out in the comments, this method will not perform as well in this kind of situation. It will usually work better (for SQL Server at least) in situations where you want the last row for each customer (as an example).

Solution 4

you can simply do

SELECT *, max(timestamp) FROM table

Edit: As aggregate function can't be used like this so it gives error. I guess what SquareCog had suggested was the best thing to do

SELECT * FROM table WHERE timestamp = (select max(timestamp) from table)

View more solutions

Comments

[Kyle Cronin]

That could work, but doesn't doing a join incur some performance penalty?

[SquareCog]

This works, but is awesome in its inefficiency. Assuming 10 rows in the original table, this produces (and discards) 55 rows. For 100 rows, the size of the set you are producing is 5,050. It grows really fast. Better to just do the two scans.

[Chris Noe]

Just curious, does the sub-query cause sqlite to process the table twice, or is that optimized away?

[Tom H]

@Dmitriy - Your own solution gives the same exact result if two rows have the same maximum timestamp. As for efficiency, in this simplistic example using a subquery works better, but not in cases where you want the last row grouped by another column.

[SquareCog]

Tom, you are right, I withdraw my comment about your solution not working. But I maintain that it's very inefficient. As far as grouping by another column -- huh? Just throw in "group by x". Maybe I don't understand the scenario? Generally,as a philisophy, I try to avoid near-cartesian joins.