Click here to Skip to main content
14,422,431 members
Rate this:
Please Sign up or sign in to vote.
See more:
I'm trying to query the salary with employee name but I'm unable to get results.

my query is

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 2 days ago
Rate this:
Please Sign up or sign in to vote.

Solution 1

Query to get nth Highest Salary

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

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary ASC) a
ORDER BY salary
   
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.
Yogesh Kumar Tyagi 11-Apr-14 1:13am
   
your welcome...
Hareesh Malli 25-Feb-15 0:38am
   
You did a great one but small change you have to do in "Query to get nth Lowest Salary" like this
"SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary ASC) a
ORDER BY salary desc"
SumonCse05 17-Apr-16 1:43am
   
ok
Rate this:
Please Sign up or sign in to vote.

Solution 3

Let say you want to get the employee name who earns the 4th highest salary:
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.
   
Comments
U@007 10-Apr-14 7:31am
   
thanks Q :)
SumonCse05 17-Apr-16 0:42am
   
data
Rate this:
Please Sign up or sign in to vote.

Solution 4

Please try this.

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


thanks.
   
Rate this:
Please Sign up or sign in to vote.

Solution 5

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!
:)
   
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 >=
Rate this:
Please Sign up or sign in to vote.

Solution 8

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.
   
Comments
SumonCse05 17-Apr-16 1:53am
   
ok
Rate this:
Please Sign up or sign in to vote.

Solution 10

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)

   
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
Rate this:
Please Sign up or sign in to vote.

Solution 14

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))
   
Comments
Richard Deeming 23-Jul-18 11:25am
   
Asked, answered, and solved FOUR YEARS AGO.

Stick to answering recent questions.
Rate this:
Please Sign up or sign in to vote.

Solution 16

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;
   
v2

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100