CREATE TABLE #Employee
(
EmpID int IDENTITY(1,1) NOT NULL,
Name varchar(50) NULL,
Salary decimal(10, 2) NULL,
Designation varchar(50) NULL
)
insert into #Employee(Name,Salary,Designation)
values('Yuvraj','20000.00','Trainee Software Engineer')
insert into #Employee(Name,Salary,Designation)
values('Virender','25000.00','Software Engineer')
insert into #Employee(Name,Salary,Designation)
values('Zaheer','30000.00','Senior Software Engineer')
insert into #Employee(Name,Salary,Designation)
values('Gautam','35000.00','Team Leader')
insert into #Employee(Name,Salary,Designation)
values('Harbhajan','40000.00','Team Leader')
insert into #Employee(Name,Salary,Designation)
values('Bhuvneshwar','45000.00','Project Cunsultant')
insert into #Employee(Name,Salary,Designation)
values('Varun ','50000.00','Project Manager')
select * from #Employee
Results
EmpID
Name
Salary Designation
---------------------------------------------------------------
1
Yuvraj 20000.00
Trainee Software Engineer
2
Virender 25000.00 Software
Engineer
3
Zaheer 30000.00
Senior Software Engineer
4
Gautam 35000.00
Team Leader
5
Harbhajan 40000.00 Team Leader
6
Bhuvneshwar 45000.00 Project Cunsultant
7
Varun
50000.00 Project Manager
(7 row(s) affected)
QUERY TO GET 2ND HIGHEST SALARY
SELECT MIN(Salary)'2nd Highest Salary'
FROM (SELECT DISTINCT TOP 2 Salary FROM #Employee ORDER BY Salary DESC) E
Results
2nd
Highest Salary
---------------------------------------
45000.00
(1 row(s) affected)
USING NOT IN OPERATOR
SELECT MAX(Salary)'2nd Highest Salary'
FROM #Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM #Employee)
Results
2nd
Highest Salary
---------------------------------------
45000.00
(1 row(s) affected)
Using not equals operator (<>)
SELECT MAX(Salary)'2nd Highest Salary'
FROM #Employee
WHERE Salary <> (SELECT MAX(Salary) FROM #Employee)
Results
2nd
Highest Salary
---------------------------------------
45000.00
(1 row(s) affected)
USING LESS THAN OPERATOR (<)
SELECT MAX(Salary)'2nd Highest Salary'
FROM #Employee
WHERE Salary < (SELECT MAX(Salary) FROM #Employee)
Results
2nd
Highest Salary
---------------------------------------
45000.00
(1 row(s) affected)
==
SELECT Salary'2nd Highest Salary'
FROM #Employee
WHERE Salary = (SELECT MIN(Salary)
FROM (SELECT DISTINCT TOP (2) Salary
FROM #Employee
ORDER BY Salary DESC) T
);
Results
2nd
Highest Salary
---------------------------------------
45000.00
(1 row(s) affected)
QUERY TO GET 2ND LOWEST SALARY
SELECT MAX(Salary)'2nd Lowest Salary'
FROM (SELECT DISTINCT TOP 2 Salary FROM #Employee ORDER BY Salary ASC) E
Results
2nd
Lowest Salary
---------------------------------------
25000.00
(1 row(s) affected)
USING NOT IN OPERATOR
SELECT MIN(Salary)'2nd Lowest Salary'
FROM #Employee
WHERE Salary NOT IN (SELECT MIN(Salary) FROM #Employee)
Results
2nd
Lowest Salary
---------------------------------------
25000.00
(1 row(s) affected)
USING NOT EQUALS OPERATOR (<>)
SELECT MIN(Salary)'2nd Lowest Salary'
FROM #Employee
WHERE Salary <> (SELECT MIN(Salary) FROM #Employee)
Results
2nd
Lowest Salary
---------------------------------------
25000.00
(1 row(s) affected)
USING GREATER THAN OPERATOR (>)
SELECT MIN(Salary)'2nd Lowest Salary'
FROM #Employee
WHERE Salary > (SELECT MIN(Salary) FROM #Employee)
Results
2nd
Lowest Salary
---------------------------------------
25000.00
(1 row(s) affected)
==
SELECT Salary'2nd Lowest Salary'
FROM #Employee
WHERE Salary = (SELECT MAX(Salary)
FROM (SELECT DISTINCT TOP (2) Salary
FROM #Employee
ORDER BY Salary ASC) T
);
Results
2nd
Lowest Salary
---------------------------------------
25000.00
(1 row(s) affected)
/*******************************************************************/
/***************Query
to get nth (2nd) Highest Salary***************/
/*******************************************************************/
USING ROW NUMBER
SELECT Salary'2nd Highest Salary'
FROM
(
SELECT Salary,Name,ROW_NUMBER() OVER(ORDER BY Salary DESC) As RowNum
FROM #EMPLOYEE
) As A
WHERE A.RowNum IN (2)
Results
2nd
Highest Salary
---------------------------------------
45000.00
(1 row(s) affected)
USING TOP KEYWORD
Select TOP 1 Salary'2nd Highest Salary'
from (SELECT DISTINCT TOP 2 Salary from #Employee ORDER BY Salary DESC)
a ORDER BY Salary ASC
Results
2nd
Highest Salary
---------------------------------------
45000.00
(1 row(s) affected)
USING SUB QUERY
SELECT Salary'2nd Highest Salary'
FROM #Employee Emp1
WHERE 2 = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM #Employee Emp2
WHERE Emp2.Salary >= Emp1.Salary)
Results
2nd
Highest Salary
---------------------------------------
45000.00
(1 row(s) affected)
USING OFFSET
select Salary'2nd Highest Salary'
from #Employee
ORDER BY Salary DESC
OFFSET 2-1 ROWS
FETCH NEXT 1 ROWS ONLY
Results
2nd
Highest Salary
---------------------------------------
45000.00
(1 row(s) affected)
/******************************************************************/
/***************Query
to get nth (2nd) Lowest Salary***************/
/******************************************************************/
USING ROW NUMBER
SELECT Salary'2nd Lowest Salary'
FROM
(
SELECT Salary,Name,ROW_NUMBER() OVER(ORDER BY Salary ASC) As RowNum
FROM #EMPLOYEE
) As A
WHERE A.RowNum IN (2)
Results
2nd
Lowest Salary
---------------------------------------
25000.00
(1 row(s) affected)
USING TOP KEYWORD
Select TOP 1 Salary'2nd Lowest Salary'
from (SELECT DISTINCT TOP 2 Salary from #Employee ORDER BY Salary ASC)
a ORDER BY Salary DESC
Results
2nd
Lowest Salary
---------------------------------------
25000.00
(1 row(s) affected)
USING SUB QUERY
SELECT Salary'2nd Lowest Salary'
FROM #Employee Emp1
WHERE 2 = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM #Employee Emp2
WHERE Emp2.Salary <= Emp1.Salary)
Results
2nd
Lowest Salary
---------------------------------------
25000.00
(1 row(s) affected)
USING OFFSET
select Salary'2nd Lowest Salary'
from #EMPLOYEE
ORDER BY Salary ASC
OFFSET 2-1 ROWS
FETCH NEXT 1 ROWS ONLY
Results
2nd
Lowest Salary
---------------------------------------
25000.00
(1 row(s) affected)
Note:-
Simply
you can change the highlighted number to desired number to get that position
highest/lowest salary.
No comments:
Post a Comment