Q:

Get the second highest value with Standard SQL

Consider a database with one table called user having two fields:

"id" (type: INTEGER, PRIMARY KEY)
"name" (type: VARCHAR(32))

Write a standard SQL query which retrieves the second highest value of id from the user table. The value returned should be represented using the column name id.
Which is standard SQL?
select id from user order by id desc limit 1,1;
select user.id as id from user order by user.id desc limit 1,1;
If both of them were wrong, and then correct answer?

A:

As far as I know, neither TOP nor LIMIT are defined by the SQL standard (at least I could not find it).
So the only pure standard solution would be to use window functions:
select *
from (
   select id, 
          row_number() over (order by id desc) as rn
   from "user"
) as t 
where rn = 2;

