Select the last 3 records for each ID in the table.
I have a table with a composite primary key ( ID
, Date
) as shown below.
+ ------ + ------------ + ------- + | ID | Date | Value | + ------ + ------------ + ------- + | 1 | 1433419200 | 15 | | 1 | 1433332800 | 23 | | 1 | 1433246400 | 41 | | 1 | 1433160000 | 55 | | 1 | 1432900800 | 24 | | 2 | 1433419200 | 52 | | 2 | 1433332800 | 23 | | 2 | 1433246400 | 39 | | 2 | 1433160000 | 22 | | 3 | 1433419200 | 11 | | 3 | 1433246400 | 58 | | ... | ... | ... | + ------ + ------------ + ------- +
The column Date
has a separate index. The table is of moderate size, currently ~ 600K rows and is growing by ~ 2K rows annually.
I want to make one SELECT query that returns the last 3 records (sorted by Date
timestamp) for each ID
. Each given ID
value is Date
always unique, so no need to worry about relationships for Date
here.
I tried the self-plugging approach inspired by this answer , but it took quite a few seconds to run and nothing came back:
SELECT p1.ID, p1.Date, p1.Value FROM MyTable AS p1
LEFT JOIN MyTable AS p2
ON p1.ID=p2.ID AND p1.Date<=p2.Date
GROUP BY p1.ID
HAVING COUNT(*)<=5
ORDER BY p1.ID, p1.Date DESC;
What would be the quick fix here?
source to share
You can see the last three dates for each ID:
SELECT ID, Date, Value
FROM MyTable
WHERE Date IN (SELECT Date
FROM MyTable AS T2
WHERE T2.ID = MyTable.ID
ORDER BY Date DESC
LIMIT 3)
Alternatively, look at the third most recent date for each ID and use it as a constraint:
SELECT ID, Date, Value
FROM MyTable
WHERE Date >= IFNULL((SELECT Date
FROM MyTable AS T2
WHERE T2.ID = MyTable.ID
ORDER BY Date DESC
LIMIT 1 OFFSET 2),
0)
Both queries should get good performance from the primary key index.
source to share
First, here is the correct query for the inequality method:
SELECT p1.ID, p1.Date, p1.Value
FROM MyTable p1 LEFT JOIN
MyTable AS p2
ON p1.ID = p2.ID AND p2.Date <= p1.Date
--------------------------^ fixed this condition
GROUP BY p1.ID, p1.Date, p1.Value
HAVING COUNT(*) <= 5
ORDER BY p1.ID, p1.Date DESC;
I'm not sure if there is a quick way to do this in SQLite. In most other databases, you can use the ANSI standard function row_number()
. In MySQL, you can use variables. Both of these problems are tricky in SQLite. The best solution might be to use a cursor.
The above can take advantage of the index MyTable(Id, Date)
.
source to share