Select the latest 3 records for each ID in a table

Question

I have a table with a composite primary key (ID, Date) like 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  |
|  ... |    ...     |  ...  |
+------+------------+-------+

There is also a separate index on Date column. The table is of moderate size, currently ~600k row and growing by ~2k everyday.

I want to do a single SELECT query that returns the latest 3 records (ordered by Date timestamp) for each ID. For each given ID, the Date values are always unique, so no need to worry about ties for Date here.

I've tried a self-join approach, inspired by this answer, but it took quite a few seconds to run and returned nothing:

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 a fast solution here?

score 14 · Accepted Answer · answered Jun 06 '15 at 10:30

You could look up the three most recent 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 up the third most recent date for each ID, and use it as a limit:

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's index.

this is a different approach, and very good one too. The performance is 10x better! — skyork, Jun 07 '15 at 01:47

Gordon Linoff · Answer 2 · 2015-06-05T23:19:02.223

3

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 fast way to do this in SQLite. In most other databases, you can use the ANSI standard row_number() function. In MySQL, you can use variables. Both of these are difficult in SQLite. Your best solution may be to use a cursor.

The above can benefit from an index on MyTable(Id, Date).

edited Jun 05 '15 at 23:19

answered Jun 05 '15 at 22:51

Gordon Linoff

1,242,037
58
646
786

actually the 'optional' fix is necessary. I've just tried it, without the extra two `GROUP BY` columns, it'd still return nothing. On the index point, since the `ID` and `Date` form composite primary key, there is an index on them by default. But the query takes almost 18 seconds to run on the table of ~600K rows. – skyork Jun 05 '15 at 22:58

score 0 · Answer 3 · edited May 02 '18 at 11:37

0

SELECT distinct x.ID,x.Date,X.Value
FROM ( SELECT DISTINCT ID FROM XXXTable  ) c
    CROSS APPLY (

    select top 3 A.ID,a.Date,Value,[Count] from (
    SELECT distinct ID,Date,Value, ROW_NUMBER()
    over (
        PARTITION BY ID
        order by Date
    ) AS [Count]  where c.ID = t.ID


    ) A  order by [Count] desc

edited May 02 '18 at 11:37

kara

3,205
4
20
34

answered May 02 '18 at 09:28

Menandro Ocampo

1

Select the latest 3 records for each ID in a table

3 Answers3