Tuesday, June 25, 2013

How to find 2nd, 3rd and N highest salary in Sql Server from Employee Table

Introduction: In this article I will explain how we can find the 2nd, 3rd and N highest salary from Employee Table.
Description:
In the previous article i have explained How to insert data of one Table to Another Table in Sql Server, What is Join in Sql Server.
I have created a table name EMPLOYEE_INFORMATION.
EMPLOYEE_ID
int
EMPLOYEE_NAME
varchar(50)
EMPLOYEE_SALARY
int
EMPLOYEE_DEPARTMENT
varchar(50)

Table contain the below mention data as in snapshot:


To find 2nd highest Salary of Employee from Table:

select MAX(EMPLOYEE_SALARY) from dbo.EMPLOYEE_INFORMATION where EMPLOYEE_SALARY < (select MAX(EMPLOYEE_SALARY) from dbo.EMPLOYEE_INFORMATION)


To find N highest Salary of Employee from Table:

SELECT TOP 1 EMPLOYEE_SALARY FROM(SELECT DISTINCT TOP N EMPLOYEE_SALARY FROM dbo.EMPLOYEE_INFORMATION ORDER BY EMPLOYEE_SALARY DESC) a ORDER BY EMPLOYEE_SALARY

Here replace N with number of which you want to find.

Example: Here I run two query together.
SELECT TOP 1 EMPLOYEE_SALARY FROM(SELECT DISTINCT TOP 4 EMPLOYEE_SALARY FROM dbo.EMPLOYEE_INFORMATION ORDER BY EMPLOYEE_SALARY DESC)A ORDER BY EMPLOYEE_SALARY

SELECT * FROM dbo.EMPLOYEE_INFORMATION ORDER BY EMPLOYEE_SALARY

Output: See below attached snapshot:

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.

No comments:

Post a Comment