选择列中具有第二高值的行

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.

我想选择时间第二高的项目组。在此示例中，我的查询应拉出“B”项。我知道我可以做最大（时间）选择“A”项，但我不知道我会怎么做到最后。

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

我的“时间”列存储为TIMESTAMP，如果这意味着什么。

9 个解决方案

#1

You can try something like:

您可以尝试以下方法：

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

SQLFiddle Demo

SQLFiddle演示

#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)

这使用内联视图（指定别名为m）来返回第二个“最大”时间值。 GROUP BY为我们提供了一个独特的列表，ORDER BY DESC将最新的列表放在第一位，而“技巧”则是LIMIT，它返回第二行。 LIMIT（m，n）=（跳过前m行，返回n行）

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.)

使用具有领先时间列的索引将增强性能。 （我认为MySQL应该能够避免“使用filesort”操作，并且可以非常快速地从内联视图查询中获取结果。）

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.

但是，如果添加了，那么如果时间超过30天，查询将不会返回“第二个最新”组。

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.)

SELECT MAX（时间）WHERE时间<（SELECT MAX（时间）方法获得第二个最新值（其他答案中给出的方法）可能更快，特别是如果没有带有前导时间列的索引，但性能最好是通过实际测试来衡量。具有领先时间列的索引也将加速MAX（）方法。）

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.

我提供的查询可以通过更改LIMIT子句... LIMIT（3,1），LIMIT（41,1）等轻松扩展到第4个最新，第42个最新等。

#3

This should give you second biggest time:

这应该给你第二大时间：

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

#4

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
                                    )
                )

#5

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

使用以下查询获取第二，第三，第四......第N个最高值：

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:

用你的数字代替N，即第二个最高值N = 2，第三个最高值N = 3，依此类推。因此，对于第二高价值使用：

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

#6

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)

#7

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.

您可以通过更改LIMIT的第一个参数来选择是否应使用第二个，第三个......最高值。

#8

MYSQL: limit base solution

MYSQL：限制基础解决方案

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

示例：记录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

LIMIT 2,1意味着它将返回第3个最高数字，因为LIMIT 1,1返回第2个最高数字，依此类推

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

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

#9

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

Leftfield-ish答案，但这允许您根据需要从单个表中选择n-1个有序值（由@Uueerdo提供https://*.com/a/35234692/618320答案），几乎没有额外的成本。

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

您可以使用GROUP_CONCAT（DISTINCT ...），然后使用一些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,

内部查询将为您提供单行结果，如“08-16-2013 00：00：00,08-13-2013 00：00：00,01-13-2012 00:00:00”，以及拆分串起我们的方式，给你一个表格结果，如，

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