TechTips

DB2 SQL FIND nth HIGHEST NUMBER.


We can use very simple correlated query to fine nth highest or lowest record in a table. Here is the query to find 2nd highest salary from EMPLOYEE table

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


Number 2 in “where 2 in” part of query is nth factor of query. To find 3rd highest salary replace this “2” with “3” and so on. Basic concept is same again subquery “Select count(*) from EMPLOYEE E2 where E1.salary <= E2.salary” uses a correlated reference E1.salary to outer query.

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

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


IBM i developer.

View Comments
There are currently no comments.