DB2 SQL FIND nth HIGHEST NUMBER.

We can use a very simple correlated query to find the nth highest or lowest record in a table.

Here is the query to find 2nd highest salary from the EMPLOYEE table

Select * from EMPLOYEE E1 where 2 = (Select count(*) from EMPLOYEE E2 where E1.salary <= E2.salary)

Number 2 in the “where 2 in” part of the query is the nth factor of the query. To find the 3rd highest salary replace this “2” with “3” and so on.

The basic concept is the same subquery “Select count(*) from EMPLOYEE E2 where E1.salary <= E2.salary” uses a correlated reference E1.salary to the outer query.

Just switch the “<=” to “>=” to get the nth lowest record.

Select * from EMPLOYEE E1 where 2 = (Select count(*) from EMPLOYEE E2 where E1.salary >= E2.salary)