MySQL - for every ID in the list select 2 last element

Question

I have a table with a following schema:

id, field, time
3, 'test0', 2012-08-29
3, 'test1', 2012-08-30
3, 'test2', 2012-08-31
2, 'test0', 2012-08-19
2, 'test1', 2012-08-20
2, 'test2', 2012-08-26
...

I need for every id in the list find it's last and previous to last value. For example if the ids = [2,3] the result should return

3, 'test1', 2012-08-30
3, 'test2', 2012-08-31 
2, 'test1', 2012-08-20
2, 'test2', 2012-08-26

If i will need just last value, I would use

SELECT *
FROM table
WHERE id IN (2, 3)
GROUP BY id

Any ideas how I can achieve this?

Answer 1

If your times for each id number are unique, this will work.

SELECT d.id, d.field,d.time
  FROM d d
  JOIN (
    SELECT max(d.time)m, d.id
      FROM d
      JOIN (
         SELECT max(time) m, id
           FROM d
       GROUP BY id
      )e ON d.id=e.id AND e.m>d.time
      GROUP BY d.id
   )e ON d.time >= e.m AND d.id = e.id

Here's how this works. This nested query gets the latest time for each id.

         SELECT max(time) m, id
           FROM d
       GROUP BY id

Then, in turn this gets nested into this query, which gives you the second-to-latest time for each id (the latest time of the subset of rows that exclude the very latest time).

    SELECT max(d.time)m, d.id
      FROM d
      JOIN (
         SELECT max(time) m, id
           FROM d
       GROUP BY id
      )e ON d.id=e.id AND e.m > d.time

Finally, the full query (shown first above) gets all the rows with times greater than or equal to the second latest time.

If your times aren't unique -- that is, you have some rows where the identical ID and time appear, you may get more than two rows for each ID. But you'll always get the two latest times.

If a particular ID only has one row, you won't get it.

Only you know if those limitations are acceptable.

Go fiddle! http://sqlfiddle.com/#!2/82719/5/0