September 1, 2012

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

Question by Salvador Dali

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 by Starx

Give this a 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 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;

Reference

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!