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;
