获得最大值之前的值 [英] Obtain value preceding maximum value

问题描述

例如，给定此稀疏ID表:

For example, given this table of sparse ids:

|id|
| 1|
| 2|
| 3|
| 6|
| 7|

我可以使用以下查询从表中获取最高的"id":

I can obtain the highest "id" from my table using this query:

SELECT max(id) FROM Comics

我得到:

|id|
| 7|

如何获得刚好在最高"id"之前的"id"(即使值不是连续的)?

How can I get the "id" just preceding the highest "id" (even if the values aren't continuous)?

推荐答案

SELECT max(id) FROM Comics

与

SELECT TOP 1 id FROM Comics ORDER BY ID DESC

注意:这是事务SQL语法，根据您的供应商使用行号或限制

note: this is transact sql syntax, use rownum or limit depending on your vendor

要获得第2行，您可以做

to get row 2 you can do

SELECT TOP 1 ID 
FROM 
    (SELECT TOP 2 id 
     FROM Comics 
     ORDER BY ID DESC) 
ORDER BY ID ASC

这篇关于获得最大值之前的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！