SELECT rows with the second highest value in a column

You can try something like:

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

SQLFiddle Demo

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.

This should give you second biggest time:

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