Obtain value preceding maximum value

问题:

For example, given this table of sparse ids:

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

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

SELECT max(id) FROM Comics

I get:

|id|
| 7|

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

回答1:

SELECT max(id) FROM Comics

is the same as

SELECT TOP 1 id FROM Comics ORDER BY ID DESC

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

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

回答2:

In general terms, you could first find the maximum id (which you've done), then find the maximum id that is less than (<) the maximum.

Specifically,

select max(id) from Comics where id < 7

Alternately, you can order the results in descending order:

select id from Comics order by id desc

and then look at the second row returned.

回答3:

this would do it as well ..

SELECT
    max(id)
FROM
    Comics 
WHERE id < (SELECT max(id) FROM Comics)