Click here to Skip to main content
15,867,750 members
Please Sign up or sign in to vote.
1.21/5 (4 votes)
See more:
I'm trying to query the salary with employee name but I'm unable to get results.

my query is

SQL
Select Min(Salary) as Highest_Salary From (Select Top 1 * From employee Order by Salary Desc) Employee


sql query for nth highest & lowest salary with employee name?

thanks in advance.
Posted
Updated 17-Jan-20 5:33am

Query to get nth Highest Salary

SQL
SELECT TOP 1 salary,Name
FROM (
SELECT DISTINCT TOP n salary,Name
FROM employee
ORDER BY salary DESC) a
ORDER BY salary


Query to get nth Lowest Salary

SQL
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary ASC) a
ORDER BY salary
 
Share this answer
 
v3
Comments
U@007 10-Apr-14 7:31am    
thank Q :)
Yogesh Kumar Tyagi 10-Apr-14 7:39am    
Most Welcome...
U@007 11-Apr-14 1:02am    
hi yohesh, in nth Highest and Lowest Salary query what is the difination of a .
Yogesh Kumar Tyagi 11-Apr-14 1:09am    
It is temporarily aliases of table
U@007 11-Apr-14 1:11am    
Ok. Thank Q.
:)
have a great day.
Let say you want to get the employee name who earns the 4th highest salary:
SQL
with cte (empname, salary)
as
(
select top 4 empname, salary from table1 order by salary desc
) select empname, salary from cte where salary =
(select min(salary) from cte)

The idea is first sort the salary by descending order, then pick the top 4, then get the min of these 4.
You should try to find the answer to the second part of your question. Do the reverse.
 
Share this answer
 
Comments
U@007 10-Apr-14 7:31am    
thanks Q :)
SumonCse05 17-Apr-16 0:42am    
data
Please try this.

SQL
select name,salary from employee where salary in((select max(salary) from employee) ,
(select min(salary) from employee))


thanks.
 
Share this answer
 
check this...
you can eliminate with clause and replace tbl_emp with your table name
check query in below comment... sorry it's gives error while adding sql statement in answer that's why I have post query in comments

Happy Coding!
:)
 
Share this answer
 
Comments
Aarti Meswania 10-Apr-14 8:56am    
DECLARE @nthLowest int = 3
DECLARE @nthHighest int = 3
;WITH tbl_emp as
(
SELECT 27000 as sal,'emp 01' as emp union ALL
SELECT 2,'emp 02' union ALL
SELECT 3,'emp 03'
)
SELECT sal, emp FROM
(
SELECT sal, emp ,
row_number() OVER ( order by sal,Emp) as sal_asc_order,
row_number() OVER ( order by sal DESC,Emp ) as sal_desc_order
FROM tbl_emp
) as temp
WHERE sal_asc_order = @nthLowest -- you can also use <=
--WHERE sal_desc_order = @nthHighest -- you can also use >=
select Salary from emp e where 4=(select Count(Salary)from emp d where e.Salary<=d.Salary)

Just replace 4 to any number you want to get highest salary of the specified number.
 
Share this answer
 
Comments
SumonCse05 17-Apr-16 1:53am    
ok

For Highest nth salary:-


select MAX(salary) from emp_sal where salary not in(select top(n-1)salary from emp_sal order by salary desc)

 
Share this answer
 
Comments
SumonCse05 15-Feb-16 2:09am    
Good ans
SumonCse05 15-Feb-16 2:18am    
Well Known solution
SumonCse05 17-Apr-16 2:01am    
ok
SumonCse05 17-Apr-16 2:02am    
ok
There are mention by First one is Highest Second Value Display in our table using this mysql query.

then, Second one is Lowest Second Value in our table using this mysql query.



1) SELECT empname, emp_salary FROM tbl_employee WHERE emp_salary = (SELECT MAX(emp_salary) FROM tbl_employee WHERE emp_salary < (SELECT MAX(emp_salary) FROM tbl_employee))

2) SELECT empname, emp_salary FROM tbl_employee WHERE emp_salary = (SELECT MIN(emp_salary) FROM tbl_employee WHERE emp_salary > (SELECT MIN(emp_salary) FROM tbl_employee))
 
Share this answer
 
Comments
Richard Deeming 23-Jul-18 11:25am    
Asked, answered, and solved FOUR YEARS AGO.

Stick to answering recent questions.
Demo Script
IF OBJECT_ID('Tempdb..#Employee') IS NOT NULL
    DROP TABLE #Employee;
 
CREATE TABLE #Employee
(EmployeeName VARCHAR(75), Salary MONEY); 
 
INSERT INTO #Employee 
VALUES
 ('A', 10000)
,('B', 20000)
,('C', 30000)
,('D', 30000)
,('E', 20000)
,('F', 40000)
,('G', 5000); 
 
SELECT * FROM #Employee;


Finding Nth Highest Salary
--Nth Highest Salary
DECLARE @Nth_Highest_Salary INT;
SET @Nth_Highest_Salary=3;
 
SELECT Salary AS Nth_Highest_Salary FROM
(
    SELECT DISTINCT Salary,DENSE_RANK() OVER(ORDER BY Salary DESC) AS R_NO FROM #Employee
) A
WHERE R_NO=@Nth_Highest_Salary;


Finding Nth Lowest Salary
--Nth Lowest Salary
DECLARE @Nth_Lowest_Salary INT;
SET @Nth_Lowest_Salary=2;
 
SELECT Salary AS Nth_Lowest_Salary FROM
(
    SELECT DISTINCT Salary,DENSE_RANK() OVER(ORDER BY Salary ASC) AS R_NO 
    FROM #Employee
) A
WHERE R_NO=@Nth_Lowest_Salary;
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900