CODEWITHSUNDEEP
mysqlsql
Salvador Dali
Salvador Dali

Reputation: 222751

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

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?

Upvotes: 6

Views: 2685

Answers (2)

Starx
Starx

Reputation: 79021

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

Upvotes: 1

O. Jones
O. Jones

Reputation: 108776

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

Upvotes: 3

Related Questions