MySQL - for each identifier in the list, select the 2 last item

Question

I have a table with the 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 each identifier in the list to find its last and previous value. For example, if identifiers = [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 need only the 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 time for each ID number is unique, this will work.

 SELECT d.id, d.field,d.time FROM dd 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 em>d.time GROUP BY d.id )e ON d.time >= em AND d.id = e.id 

Here's how it works. This subquery receives the latest time for each identifier.

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

Then, in turn, it is nested in this request, which gives you the second-highest time for each identifier (the last time is a subset of the rows that excludes the most recent 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 em > d.time 

Finally, the full query (shown above) receives all rows with a time greater than or equal to the second last time.

If your times are not unique, that is, you have several lines where the identical identifier and time are displayed, you can get more than two lines for each identifier. But you always get the last two times.

If a particular identifier has only one line, you will not receive it.

Only you know if these restrictions are acceptable.

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

Answer 2

Try

 SELECT * FROM table WHERE id = '1' ORDER BY `id` desc LIMIT 0,2 UNION SELECT * FROM table WHERE id = '2' ORDER BY `id` desc LIMIT 0,2 

UPDATE:

If you can also try something like this:

 SELECT t1.* FROM `tablename` t1 LEFT OUTER JOIN `tablename` t2 ON (t1.id = t2.id AND t1.time > t2.time) GROUP BY t1.id, t1.field, c1.time HAVING COUNT(*) < 2; 

Link