Select the latest 3 records for each ID in a table

You could look up the three most recent dates for each ID:

SELECT ID, Date, ValueFROM MyTableWHERE 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, ValueFROM MyTableWHERE 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.

First, here is the correct query for the inequality method:

SELECT p1.ID, p1.Date, p1.ValueFROM MyTable p1 LEFT JOIN     MyTable AS p2      ON p1.ID = p2.ID AND p2.Date <= p1.Date--------------------------^ fixed this conditionGROUP BY p1.ID, p1.Date, p1.ValueHAVING COUNT(*) <= 5ORDER 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).

SELECT distinct x.ID,x.Date,X.ValueFROM ( 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