sqlite: SQL: overcoming no ORDER BY in nested query

lundi 22 juin 2015

I have a table t as follows:

CREATE TABLE t(
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    date    TEXT,
    a       TEXT,
    b       TEXT
);

Sample Data

1|2015-06-15|a1 15|b1 15
2|2015-06-15|a2 15|b2 15
3|2015-06-16|a1 16|b1 16
4|2015-06-16|a2 16|b2 16
5|2015-06-17|a1 17|b1 17
6|2015-06-17|a2 17|b2 17

I want to select all values of a and b for the two latest dates in the table.

I want to do something like the following:

SELECT a,b FROM t WHERE date IN (SELECT DISTINCT date FROM t ORDER BY date DESC LIMIT 2);

Expected Output

a1 17|b1 17
a2 17|b2 17

but you aren't allowed ORDER BY in a nested query. How can this be done in a single query?

sqlite