如何在 SQL 中返回第二个最新记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8590296/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return second newest record in SQL?
提问by Davor Zubak
If this:
如果这：
SELECT *
FROM Table
WHERE Date=( SELECT MAX(Date)
FROM Table
)
returns newest record from the table, how to get second newestrecord?
从表中返回最新记录，如何获取第二个最新记录？
回答by ypercube??
SELECT *
FROM Table
WHERE Date = ( SELECT MAX(Date)
FROM Table
WHERE Date < ( SELECT MAX(Date)
FROM Table
)
) ;
or:
或者：
SELECT TOP (1) *
FROM Table
WHERE Date < ( SELECT MAX(Date)
FROM Table
)
ORDER BY Date DESC ;
or:
或者：
SELECT *
FROM
( SELECT t.*
, ROW_NUMBER() OVER(ORDER BY Date DESC) AS RowNumber
FROM Table t
) AS tmp
WHERE RowNumber = 2 ;
If the Datecolumn 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.
如果该Date列具有唯一值，则所有三个查询都将给出相同的结果。如果该列可以有重复的日期，那么它们可能会给出不同的结果（当第一或第二名有关系时）。如果第二名有关系，第一个查询甚至会在结果中给出多行。
回答by Quasdunk
"select TOP (1) *
from Table
WHERE Date<(SELECT MAX(Date) FROM Table)
ORDER BY Date DESC"
should do the trick.
应该做的伎俩。
回答by Prasad Rajapaksha
Please check this code.
请检查此代码。
SELECT * FROM category
WHERE Created_Time <(
SELECT MAX(Created_Time)
FROM category
)
ORDER BY Created_Time DESC
LIMIT 1
Prasad.
普拉萨德。
回答by shaijut
select second last date in sql:
在sql中选择倒数第二个日期：
SELECT MAX(YourDateColumn) FROM YourTable where ColumnId=2 and YourDateColumn <
(SELECT MAX(YourDateColumn) FROM YourTable where ColumnId=2)
hope helps someone.
希望能帮助某人。
回答by saroj
Try this
尝试这个
SELECT *
FROM Table
WHERE Date = ( SELECT MAX(Date) FROM Table
WHERE Date < ( SELECT MAX(Date) FROM Table)
) ;
