Common Interview Question as well.
There are various ways to do find the second highest.
eg:
1. select MAX(Column) from TABLE
where Column NOT IN
(select Max(Column) from TABLE)
2. select MAX(Column) from TABLE
where Column <
(select Max(Column) from TABLE)
3. Using Minus in SQL Server
However, when we want to find the 3rd / 4th highest the query gets complicated and performance would take a hit.
A generic of doing the same would be:
SELECT
FROM (
select ROW_NUMBER() OVER (ORDER BY Column DESC) A,
from table
) TAB
WHERE A = N
(where N is the required value)
If you know more efficient ways then please put in a comment.
