SELECT rows with the second highest value in a column [duplicate]

Question

Let's say I have a table similar to the following:

Item         Description            Time
-----        -----------            -----
ItemA1       descript               08-16-2013 00:00:00
ItemA2       descript               08-16-2013 00:00:00
ItemA3       descript               08-16-2013 00:00:00
.
.
ItemAN       descript               08-16-2013 00:00:00

ItemB1       descript               08-13-2013 00:00:00
ItemB2       descript               08-13-2013 00:00:00
ItemB3       descript               08-13-2013 00:00:00
.
.
ItemBN       descript               08-13-2013 00:00:00
.
.
.
ItemX1       descript               01-13-2012 00:00:00
ItemX2       descript               01-13-2012 00:00:00
ItemX3       descript               01-13-2012 00:00:00
.
.
ItemXN       descript               01-13-2012 00:00:00

Groups of items are added periodically. When a group of items is added they are all added with the same "Time" field. "Time" essentially serves as a unique index for that item group.

I want to SELECT the group of items that have the second highest time. In this example my query should pull the "B" items. I know I can do max(time) to SELECT the "A" items, but I don't know how I would do second last.

My "Time" columns are stored as TIMESTAMP if that means anything.

Answer 1

You can try something like:

SELECT MAX(Time)
FROM yourTable
WHERE Time < (SELECT MAX(Time) FROM yourTable)

SQLFiddle Demo

Answer 2

One approach:

SELECT t.*
 FROM mytable t
 JOIN ( SELECT l.time
          FROM mytable l
         GROUP BY l.time
         ORDER BY l.time DESC
         LIMIT 1,1 
      ) m
   ON m.time = t.time

This uses an inline view (assigned an alias of m) to return the second "greatest" time value. The GROUP BY gets us a distinct list, the ORDER BY DESC puts the latest first, and the "trick" is the LIMIT, which returns the second row. LIMIT(m,n) = (skip first m rows, return next n rows)

With that time value, we can join back to the original table, to get all rows that have a matching time value.

---

Performance will be enhanced with an index with leading column of time. (I think MySQL should be able to avoid a "Using filesort" operation, and get the result from the inline view query fairly quickly.)

But, including a predicate in the inline view query, if you "know" that the second latest time will never be more than a certain number of days old, won't hurt performance:

   WHERE l.time > NOW() + INTERVAL -30 DAYS

But with that added, then the query won't return the "second latest" group if it's time is more than 30 days ago.

The SELECT MAX(time) WHERE time < ( SELECT MAX(time) approach to get the second latest (the approach given in other answers) might be faster, especially if there is no index with leading column of time, but performance would best be gauged by actual testing. The index with leading column of time will speed up the MAX() approach as well.)

The query I provided can be easily extended to get the 4th latest, 42nd latest, etc, by changing the LIMIT clause... LIMIT(3,1), LIMIT(41,1), etc.

Answer 3

This should give you second biggest time:

SELECT time FROM table GROUP BY time ORDER BY time DESC LIMIT 1,1