find nth highest salary in sql server

Finding nth Highest Salary from Emp table  using Row_Number.

Here is the Emp Table.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	12/17/1980 12:00:00 AM	800.00		20
7499	ALLEN	SALESMAN	7698	2/20/1981 12:00:00 AM	1600.00	300.00	30
7521	WARD	SALESMAN	7698	2/22/1981 12:00:00 AM	1250.00	500.00	30
7566	JONES	MANAGER	7839	4/2/1981 12:00:00 AM	2975.00		20
7654	MARTIN	SALESMAN	7698	9/28/1981 12:00:00 AM	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	5/1/1981 12:00:00 AM	2850.00		30
7782	CLARK	MANAGER	7839	6/9/1981 12:00:00 AM	2450.00		10
7839	KING	PRESIDENT		11/17/1981 12:00:00 AM	5000.00		10
7844	TURNER	SALESMAN	7698	9/8/1981 12:00:00 AM	1500.00	0.00	30
7900	JAMES	CLERK	7698	12/3/1981 12:00:00 AM	950.00		30
7902	FORD	ANALYST	7566	12/3/1981 12:00:00 AM	3000.00		20
7934	MILLER	CLERK	7782	1/23/1982 12:00:00 AM	1300.00		10

Note: ROW_NUMBER works with OVER In Over sort all salary column in descending order so that Highest salary will be in first row.

1.Find Highest Salary from emp table

select * from (
select ROW_NUMBER() over (order by sal desc) rownumber,ename,sal from emp ) o
where o.rownumber=1

O/P:
rownumber    ename    sal
1    KING    5000.00


2.Find Second Highest Salary from Emp Table
O/P:
rownumber    ename    sal
2    FORD    3000.00

3.Find 5th Highest Salary from Emp Table:


select * from (
select ROW_NUMBER() over (order by sal desc) rownumber,ename,sal from emp ) o
where o.rownumber=5

O/P:
rownumber    ename    sal
5    CLARK    2450.00


Like this user can find Nth Highest salary/Number of Employees in the table.


Tags: Find nth highest salary in sql server,Find nth highest salary from sql-server, using Row_number in Sql-Server,using Row_number in SQL Server,using Order by Clause in SQL Server.Sub Queries in SQL Server.