Find nth highest value using SQL Queries

Let us see how to calculate the first, second and the nth highest value.

Our employee table is as shown below.

Employee

Employee ID	Salary
1	4000
2	2500
3	4500
4	1800

To calculate the highest salary and the second highest

SELECT sal.employeeid, sal.salary FROM
   (select employeeid, salary from employee order by salary desc) sal
WHERE rownum <  3

EmployeeID        Salary
3                          4500
1                          4000

To calculate 2nd highest salary

SELECT max(salary)"Second Max Salary" FROM employee
where salary not in (select max(salary) from employee)

Second Max Salary
4000

To calculate nth highest salary(Using Rank() function) 

Select EmployeeID, Salary, RANK() OVER (ORDER BY salary desc) "RANK" from employee

EmployeeID      Salary          Rank
 3                       4500                 1
1                        4000                 2
2                        2500                 3
4                        1800                 4

In the next blog, let us apply these queries to a real data set and analyze the results.