CODEWITHSUNDEEP
sqlsqlite
funkybro
funkybro

Reputation: 8671

SQL: overcoming no ORDER BY in nested query

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?

Upvotes: 0

Views: 40

Answers (2)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

try this query

SELECT t.a,t.b FROM t 
join(SELECT DISTINCT date FROM t ORDER BY date DESC LIMIT 2) q
on t.date=q.date

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270733

Use a join instead:

SELECT a, b
FROM t JOIN
     (SELECT DISTINCT date FROM t ORDER BY date DESC LIMIT 2) tt
     on t.date = tt.date;

Upvotes: 2

Related Questions