[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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Thongchan Thananate

Thongchan Thananate

People might laugh at it or call it foolish logic, but that’s enough for me. That’s what romanticism is about!