14,422,431 members
Rate this:
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?

Posted
Updated 2 days ago

Rate this:

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

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:

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.
U@007 10-Apr-14 7:31am

thanks Q :)
SumonCse05 17-Apr-16 0:42am

data
Rate this:

Solution 4

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

thanks.
Rate this:

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!
:)
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:

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.
SumonCse05 17-Apr-16 1:53am

ok
Rate this:

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)

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:

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))
Richard Deeming 23-Jul-18 11:25am

Rate this:

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