Introduction:
While working in database it’s
very common where we need to find the 2nd, 3rd or nth highest
value of a column. I have explained different method to find out these values.
In this article i Will explain different
ways to get 2nd, 3rd,.....nth highest salary from employee table or record in SQL
server database table.
This is also the
most commonly asked question in asp.Net.
Let's create a sample table "Emp_Table "
in sql server with the columns and data typeas shown below
Column name
|
Data Type
|
Emp_Id_Pk
|
Int(Primary key)
|
Emp_Name
|
varchar(100)
|
Salary
|
decimal(18, 2)
|
Dept_id_FK
|
int
|
or
CREATE TABLE [dbo].[Emp_table](
[Emp_Id_pk] [int] IDENTITY(1,1) NOT NULL,
[Emp_Name] [varchar](100),
[Dept_id_FK] [int] NULL,
[Salary] [decimal](18, 2) NULL,
CONSTRAINT [PK_Tb_Emp] PRIMARY KEY CLUSTERED
(
[EmpId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Now insert some
data in this table and check the data in table as:
SELECT * FROM Emp_Table
First Method:
Query to get 2nd highest salary
from table
SELECT TOP 1 Salary FROM
(
SELECT DISTINCT TOP 2 Salary FROM Emp_Table ORDER BY Salary DESC
) AS T ORDER BY Salary ASC
Result:
Salary
|
18000.00
|
Query to get 3rd highest salary from table
SELECT TOP 1 Salary FROM
(
SELECT DISTINCT TOP 3 Salary FROM Emp_table ORDER BY Salary DESC
) AS T ORDER BY Salary ASC
Result:
Salary
|
16000.00
|
Similarly you can get 4th, 5th, 6th...nth salary using the
following query structure
SELECT TOP 1 Salary FROM
(
SELECT DISTINCT TOP N Salary FROM Emp_Table ORDER BY Salary DESC
) AS T ORDER BY Salary ASC
Note: Replace N with the required highest
salary of employee. E.g if you want to get 5th highest salary from the table then the above query will be simply
SELECT TOP 1 Salary FROM
(
SELECT DISTINCT TOP 5 Salary FROM Emp_table ORDER BY Salary DESC
) AS T ORDER BY Salary ASC
Result:
Salary
|
12000.00
|
2nd Method:
Query to get 2nd highest salary
from table
SELECT MAX(Salary) AS 'Salary' FROM Emp_Table
WHERE Salary NOT IN
(
SELECT DISTINCT TOP 1 (SALARY) FROM Emp_Table ORDER BY Salary DESC
)
Result:
Salary
|
18000.00
|
Query to get 3rd highest salary from table
SELECT MAX(Salary) AS 'Salary' FROM Emp_Table
WHERE Salary NOT IN
(
SELECT DISTINCT TOP 2 (SALARY) FROM Emp_Table ORDER BY Salary DESC
)
Result:
Salary
|
16000.00
|
Similarly you can get 4th, 5th, 6th...nth salary using the
following query structure
SELECT MAX(Salary) AS 'Salary' FROM Emp_Table
WHERE Salary NOT IN
(
SELECT DISTINCT TOP N-1(SALARY) FROM Emp_Table ORDER BY Salary DESC
)
Note: Replace N with the required
highest salary of employee. E.g if you want to get 5th highest salary from the table then the above query will be simply
SELECT MAX(Salary) AS 'Salary' FROM Emp_Table
WHERE Salary NOT IN
(
SELECT DISTINCT TOP 4 (SALARY) FROM Emp_Table ORDER BY Salary DESC
)
Result of the above query will
be:
Salary
|
18000.00
|
3rd Method:
query to get 2nd highest salary
from table
SELECT MIN(Salary) AS 'Salary' FROM Emp_Table
WHERE Salary IN
(
SELECT DISTINCT TOP 2 Salary FROM Emp_Table ORDER BY Salary DESC
)
Result:
Salary
|
16000.00
|
Similarly you can get 4th, 5th, 6th...nth salary using the
following query
SELECT MIN(Salary) AS 'Salary' FROM Emp_Table
WHERE Salary IN
(
SELECT DISTINCT TOP N Salary FROM Emp_Table ORDER BY Salary DESC
)
Note: Replace N with the required
highest salary of employee
SELECT MIN(Salary) AS 'Salary' FROM Emp_Table
WHERE Salary IN
(
SELECT DISTINCT TOP 5 Salary FROM Emp_Table ORDER BY Salary DESC
)
Result:
Salary
|
16000.00
|
4th
Method :
Query to get 2nd highest salary from table
SELECT MAX(salary)AS 'Salary' FROM Emp_Table WHERE salary NOT IN (SELECT MAX(salary) FROMEmp_Table)
Result:
Salary
|
18000.00
|
5th Method:
Query to get 2nd highest salary from table
SELECT MAX(salary) AS 'Salary' FROM Emp_Table WHERE salary < (SELECT MAX(salary) FROMEmp_Table)
Result of the above query will
be:
Salary
|
18000.00
|
0 comments:
Post a Comment