如何在 SQL 中返回第二个最新记录? [英] How to return second newest record in SQL?

问题描述

如果这样:

SELECT *
FROM Table
WHERE Date=( SELECT MAX(Date)
             FROM Table
           )

从表中返回最新记录，如何获取第二个最新记录?

returns newest record from the table, how to get second newest record?

推荐答案

SELECT * 
FROM Table
WHERE Date = ( SELECT MAX(Date) 
               FROM Table
               WHERE Date < ( SELECT MAX(Date) 
                              FROM Table
                            )
             ) ;

或:

SELECT TOP (1) * 
FROM Table
WHERE Date < ( SELECT MAX(Date) 
               FROM Table
             ) 
ORDER BY Date DESC ;

或:

SELECT *
FROM
  ( SELECT t.*
         , ROW_NUMBER() OVER(ORDER BY Date DESC) AS RowNumber
    FROM Table t
  ) AS tmp
WHERE RowNumber = 2 ;

<小时>

如果 Date 列具有唯一值，则所有三个查询都将给出相同的结果.如果该列可以有重复的日期，那么它们可能会给出不同的结果(当第一或第二名有关系时).如果排在第二位，第一个查询甚至会在结果中给出多行.

---

If the Date column has unique values, all three queries will give the same result. If the column can have duplicate dates, then they may give different results (when there are ties in 1st or 2nd place). The first query will even give multiple rows in the result if there are ties in 2nd place.

这篇关于如何在 SQL 中返回第二个最新记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！