SELECT rows with the second highest value in a column

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.

You should probably test both MAX and LIMIT/ORDER BY solutions, as there can be performance downside to LIMITs, depending on availability of indexes. http://stackoverflow.com/questions/426731/min-max-vs-order-by-and-limit — Declan_K, Aug 16 '13 at 18:59

DarkAjax · Accepted Answer · 2013-08-16T19:43:34.237

20

You can try something like:

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

SQLFiddle Demo

edited Aug 16 '13 at 19:43

answered Aug 16 '13 at 18:51

DarkAjax

15,955
11
53
65

+1 for nice sql fiddle button, but your query gives not items, but just time – Roman Pekar Aug 17 '13 at 08:53
@DarkAjax, This solution doesn't scale at all. How would you select the 3rd highest then? Or 4th, 5th, 6th, nth? – Pacerier Apr 01 '15 at 10:49
This will only find the second largest value, not the ROW with the second highest value – yusong Jul 05 '19 at 04:01

spencer7593 · Answer 2 · 2013-08-16T19:14:35.917

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.

*NOTE:* the query in this answer uses MySQL specific `LIMIT` syntax, which is *not* supported in most (every) other commercial databases. — spencer7593, Aug 16 '13 at 19:15
LIMIT syntax in a subquery is not supported for earlier versions of MySQL. — Manu, Dec 13 '13 at 10:20
@Manu: And older versions of MySQL don't even support inline views or subqueries at all. — spencer7593, Dec 13 '13 at 21:43

score 6 · Answer 3 · answered Aug 16 '13 at 18:57

6

This should give you second biggest time:

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

answered Aug 16 '13 at 18:57

Aycan Yaşıt

2,106
4
34
40

score 1 · Answer 4 · answered Aug 16 '13 at 18:52

SELECT  T1.ITEM
FROM    YOURTABLE T1
WHERE   T1.TIME = ( SELECT  MAX(T2.TIME) 
                    FROM    YOURTABLE T2 
                    WHERE   T2.TIME < ( SELECT  MAX(T3.TIME) 
                                        FROM    YOURTABLE T3
                                    )
                )

score 1 · Answer 5 · answered Aug 27 '15 at 10:57

Get second, third, fourth......Nth highest value using following query:

SELECT MIN(value) from yourTable WHERE value IN( SELECT TOP N value FROM yourTable ORDER BY value DESC)

Replace N by you number i.e. N=2 for second highest value, N=3 for third highest value and so on. So for second highest value use:

SELECT MIN(value) from yourTable WHERE value IN( SELECT TOP 2 value FROM yourTable ORDER BY value DESC)

score 0 · Answer 6 · edited Aug 16 '13 at 18:54

0

Something really straightforward like this should work

select * from my-table where time = 
  (Select top 1 time
     from (select top 2 time from my-table order by time desc)
   order by time asc)

edited Aug 16 '13 at 18:54

sealz

5,348
5
40
70

answered Aug 16 '13 at 18:52

bielawski

1,466
15
20

`TOP` is Microsoft SQL Server syntax, not supported by MySQL. – spencer7593 Aug 16 '13 at 18:55

score 0 · Answer 7 · edited Oct 18 '17 at 07:41

0

Here is another solution which I think should work for your problem:

CREATE TEMPORARY TABLE xHighest AS (SELECT DISTINCT Time FROM `my-table` ORDER BY Time DESC LIMIT 1,1);

SELECT * FROM `my-table` JOIN xHighest ON (my-table.Time = xHighest.Time);

You can choose if second, third, ... highest value should be used by changing the first parameter of LIMIT.

edited Oct 18 '17 at 07:41

Regolith

2,944
9
33
50

answered Oct 18 '17 at 06:57

Bernd Schuhmacher

129
8

score 0 · Answer 8 · edited Jun 20 '20 at 09:12

0

MYSQL: limit base solution

Example: records 1, 2, 3, 4, 5.

LIMIT 2,1 means it will return 3rd highest number, as LIMIT 1,1 return 2nd highest number and so on

SELECT rc.rc_officer_id FROM recovery_complain_officer rc ORDER BY rc.rc_officer_id DESC LIMIT 2,1

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 10 '18 at 12:10

Muhammad Abbas

129
4

score 0 · Answer 9 · answered May 10 '18 at 15:24

Leftfield-ish answer, but this allows you to select n-1 ordered values from a single table as you want (credit to the https://stackoverflow.com/a/35234692/618320 answer by @Uueerdo), with nearly no extra cost.

You can use GROUP_CONCAT(DISTINCT...), followed by some SUBSTRING(...),

SELECT
  SUBSTRING_INDEX(groupTime, ',', 1) AS time1,
  SUBSTRING_INDEX(SUBSTRING_INDEX(groupTime, ',', 2), ',', -1) AS time2,
  SUBSTRING_INDEX(SUBSTRING_INDEX(groupTime, ',', 3), ',', -1) AS time3
FROM (
  SELECT GROUP_CONCAT(DISTINCT Time ORDER BY Time DESC) groupTime FROM mytable) t

The inner query would give you a single-row result back like "08-16-2013 00:00:00,08-13-2013 00:00:00,01-13-2012 00:00:00", and splitting that string up the way we are doing, gives you a table result like,

time1                 time2                time3
---------             ------------         ------------
08-16-2013 00:00:00   08-13-2013 00:00:00  01-13-2012 00:00:00

score 0 · Answer 10 · answered Jul 30 '19 at 15:30

0

Maybe TOP and START AT can help here. I have used this in Sybase SQL.

SELECT TOP 1 START AT 2 * FROM Table_Name ORDER BY CREATEDDATETIME DESC;

answered Jul 30 '19 at 15:30

Naveen Kumar

420
4
16

SELECT rows with the second highest value in a column

10 Answers10

Linked