CODEWITHSUNDEEP
sql
Sea guy
Sea guy

Reputation: 178

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?

Upvotes: 0

Views: 11316

Answers (5)

Gordon
Gordon

Reputation: 94

select 
    secondhighest.id
from 
    "user" highest
    left join "user" biggerthanhighest on 
        biggerthanhighest.id > highest.id
    left join "user" secondhighest on
        highest.id > secondhighest.id
    left join "user"  biggerthansecondhighest on
        biggerthansecondhighest.id > secondhighest.id and
        biggerthansecondhighest.id < highest.id
where
    biggerthanhighest.id is null and
    biggerthansecondhighest.id is null

will avoid top and subqueries. might be reasonably fast on really large tables?

Upvotes: 1

user330315
user330315

Reputation:

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;

Upvotes: 3

Lukas
Lukas

Reputation: 38

SELECT TOP 1 "id" FROM (SELECT TOP 2 "id" FROM "user" ORDER BY "id" DESC) AS "wt" ORDER BY "id"

Upvotes: 0

mgmedick
mgmedick

Reputation: 700

SELECT TOP 1 id
FROM (
SELECT DISTINCT TOP 2 id
FROM user
ORDER BY id DESC) a
ORDER BY id

This is pretty standard as well I would think.

Upvotes: 0

sathish
sathish

Reputation: 36

SELECT MAX( id) FROM user where id !=( SELECT max (id) FROM user )

Upvotes: 1

Related Questions