[365Gist] select 2nd highest, again? — SQL

This SQL problem is quite popular among those who called themselves ‘beginner’ and ‘advance’

The approach is somewhat easy —

SELECT someinteger
FROM sometable
ORDER BY someinteger DESC
LIMIT 1 OFFSET 1;

Here you go, the second highest value of ‘someinteger’

BUT..!
What if it’s a trap!?

Some table fools us because it had only one record.
(You will ask yourself, and why the f I have to sort for second highest?)
Well, interviewers like this kind of trick.

So, to prevent that from error and being as an ‘advance’ SQL-er

There are two approaches I can think about to solve to problems

1st approach — using sub query

SELECT
(
SELECT DISTINCT someinteger
FROM sometable
ORDER BY someinteger DESC
LIMIT 1 OFFSET 1
) AS SecondHighest
;

2nd approach — using IFNULL to make sure if it’s null it still return the value

SELECT
IFNULL
(
SELECT DISTINCT someinteger
FROM sometable
ORDER BY someinteger DESC
LIMIT 1 OFFSET 1
) AS SecondHighest
;

That’s all

There’re some other approaches rather than OFFSET for example:

SELECT TOP 1 someinteger
FROM (
SELECT TOP 2 someinteger
FROM sometable
ORDER BY someinteger DESC
) ORDER BY someinterger ASC
;

or

WITH resulttemptable AS (
SELECT someinteger, DENSE_RANK() OVER (ORDER BY someinteger DESC AS denserank)
FROM sometable)
SELECT TOP 1 someinteger
FROM resulttemptable
WHERE resulttemptable.denserank = 2
;

Ready, Set, Sequel!

By the way, in real life, we will smack them with Excel.