Search code examples
mysqlcorrelated-subquery

SELECT second highest timestamp and correlative columns

In a table with bags, where each bag have a location column and time column, I can get the most recent location with this query:

SELECT b.* 
FROM bag_position b 
WHERE time = ( 
    SELECT MAX(c.time) 
    FROM bag_position c 
    WHERE c.bag_id = b.bag_id 
);

How can I get the second highest location? I tried with this, but it doesn't work:

SELECT b.*
from bag_position b
where time = ( select max(c.time) from bag_position c where c.time < (select max d.time from bag_position d) and c.bag_id = b.bag_id );
Solution

  • You can use offset and limit instead of max():

    select b.*
from bag_position b
where b.time = (select b2.time
                from bag_position b2
                where b2.bag_id = b.bag_id
                order by time desc
                limit 1 offset 1
               );