This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.
Please read my article here to find Nth Highest Salary of Employee table : SQL SERVER – Query to Retrieve the Nth Maximum value
I have re-wrote the same article here with example of SQL Server 2005 Database AdventureWorks : SQL SERVER – 2005 – Find Nth Highest Record from Database Table
Just a day ago, I have received another script to get the same result from one of the blog reader Pravin Phatangare, let us see his simple method here.
For particular example of employee :
How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table
The following solution is for getting 6th highest salary from Employee table ,
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary
You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary
where n > 1 (n is always greater than one)
Same example converted in SQL Server 2005 to work with Database AdventureWorks.
USE AdventureWorks; GO SELECT TOP 1 Rate FROM ( SELECT DISTINCT TOP 4 Rate FROM HumanResources.EmployeePayHistory ORDER BY Rate DESC) A ORDER BY Rate GO
Reference : Pinal Dave (https://blog.sqlauthority.com), Pravin Phatangare
338 Comments. Leave new
Hi Pinal,
myself Dharmendra, and am a Regular reader of ‘SQlAuthority’.
its a Good one Article about finding nth Higest Salary..
if am not wrong we can also do the same thing by using aggreate function like Min or Max..
Like if i want to know 4th Highest Salary then i can
SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT TOP 4 Sal FROM TableName ORDER BY Sal DESC)
Regards
Dharmendra k Dixit
thanx dixit sir
hi Dharmendhra, this is sekhar. you r absolutely correct. It works.
Thank u very much Dixit
Thanks dixit sir for co-operation.
Thank you Dharmendra Because it is very useful for any reader can read this one …
I really Satiesfied to do this
Dude
Dude if we have same salaries like 3000 for two employees then your query count the same salary twice. So we cannot use this query
Try Distinct in the subquery.
SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT DISTINCT TOP 4 Sal FROM TableName ORDER BY Sal DESC)
u r absolutely correct
SELECT MAX(SAL) AS SAL FROM EMP WHERE SAL(SELECT MAX(SAL) FROM EMP)
This is same as
SELECT MAX(SAL) AS SAL FROM EMP
SELECT MAX(SAL) AS SAL FROM EMP WHERE SAL(SELECT MAX(SAL) FROM EMP) its not working make it correct
SELECT MAX(SAL) AS SAL FROM EMP WHERE SAL NOT IN
(SELECT MAX(SAL) FROM EMP) its not working make it correct
Find lot of methods at
SELECT MAX(SAL) AS SAL FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP)
try this
WOWWWWWWWWWWWWWWW DIXIT YOU ARE RITE..
IT WORKED FINE……… THANKS
nothing like wowwwwww
–CREATE TABLE EMPLOYEE( EMPLID NUMBER,
EFFDT DATE)
— insert into EMPLOYEE
— values(99, to_date(‘1998/03/31:12:01:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’));
SELECT * FROM EMPLOYEE
SELECT * FROM EMPLOYEE E WHERE 1= ( SELECT COUNT(DISTINCT E1.EFFDT) FROM EMPLOYEE E1
WHERE E1.emplid = E.emplid AND E1.effdt >= E.effdt
AND E1.EFFDT < = SYSDATE
)
Hi Dixit,
Your query is working fine. But I think this is not used for same salaried employees. Distinction should be mentioned there.
Thanks.
How about this?
SELECT * FROM EMPLOYEE WHERE ESAL = (
SELECT ESAL FROM (
SELECT DISTINCT DENSE_RANK() OVER(ORDER BY ESAL DESC) AS [RANK], ESAL FROM EMPLOYEE )AS A WHERE [RANK] = 3);
When I compare to your query, mine runs 4 % faster but it is on very small dataset of 5 employees. Don’t know how it will react to bigger datasets.
You query is ran as follows,
SELECT * FROM EMPLOYEE WHERE ESAL IN
(
SELECT MIN(ESAL) FROM EMPLOYEE WHERE ESAL IN
(SELECT DISTINCT TOP 3 ESAL FROM EMPLOYEE ORDER BY ESAL DESC)
)
How about this,
SELECT * FROM EMPLOYEE WHERE ESAL = (
SELECT ESAL FROM (
SELECT DISTINCT DENSE_RANK() OVER(ORDER BY ESAL DESC) AS [RANK], ESAL FROM EMPLOYEE )AS A WHERE [RANK] = 3);
My above query works 4% faster than yours on small dataset of 5 employees
Ya it is easily understand thanks
Hi Dixit, You are not right. because it could be possibility that some salary also duplicate ..so you should use distinct keword as well.
select sal from sal order by desc limit nth,1;
i.e n=4 ,fifth highest sal will show.
from Shashidhar Tiwari
Works like a Charm!!
I think this question is one of the highest hits in interviews…
Thanks.
how can i get second hight salary or 3 rd hight salary
here
iam giving example
select top 1 quantity from(SELECT TOP n-1 quantity FROM (SELECT DISTINCT TOP n quantity FROM productorder ORDER BY quantity DESC) as A ORDER BY quantity desc )as B order by quantity
n->represt top nth records
n-1 is the which highest salary you want
@ Dharmendar
Bro.. your code is quite long and might be resource consuming because ,
1. your are selecting the minimum from a whole bunch of values.
2. You need to write Distinct in the query only then you will give the correct answer.
Like your code should be :
SELECT MIN (SALARY )
FROM UEXAMPLE1
WHERE SALARY IN (SELECT DISTINCT TOP 4 SALARY FROM UEXAMPLE1 ORDER BY SALARY DESC)
Yes Br. u r correct.
u people are excellent
HELLO !
THIS QUESTION IS DIFFICULT .
BUT I TRY THE ANSWER
SELECT TOP 5 * FROM EMPLOYEE ORDER BY EMP_SALARY DESC; ——–> (THIS IS USED FOR RETRIEVE THE MAXIMUM TOP 5 PAID EMPLOYEES )
OR
TO RETRIEVE THE MIN VALUE USE THIS
SELECT TOP 5 * FROM EMPLOYEE ORDER BY EMP_SALARY;
THANK YOU.
its not working
so post right ans
Nice.
It really helps a lot.
Thanks.
To Imran..
Yes Bro ..Thanks for ur Suggestion..
it might be resource Consuming..
but i think so, its an alternative for finding nth Highest Salary..
looking forward ur openion..
Regards
Dharmendra k Dixit
hi there guys i need a query but i can´t get it
here i go the table has department_id, salary, employee_id
how can i increase the salary of all employees of a department in the min salary paid in that department
as example department 1 the min sal paid is 1000, i have to paid now to each employee his salary +1000
hi
u can try this….
UPDATE emp
SET sal = sal +
(SELECT MIN(sal)
FROM emp E2
WHERE e2.deptid = emp.deptid
GROUP BY deptid)
I think following query will give second highest salary. You can get nth highest by replacing 2 with n.
select max(sal)from emp e1 where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal);
Hi Chirag,
Can u plz let me know how the Query works,
Cheers,
Sunil
Yours answer is the best
select top 1 * from (select top 2 * from tabname order by sal desc )tabname order by sal asc
super…
very nice…
I think this query won’t if more than one employee have the same salary.
It would not work on following table.
e1 100
e2 200
e3 300
e4 300
Hi Chirag, I am not agree with this query bcoz this query have a limitation.
e.g if have a same salary for both employee then this query will display unexpected results
Hi,
I am Praveen. I am visiting this site for first time. The above query is really good and generic. We can use this also to get nth lowest salary by just changing the order from DESC to ASC.
haiiiiiiiii sir it worked…..
hi
i hv tried with this query but i am not able to get the exat answer
i tried with this query it works fine
===========================
put any digit instead of 5 u can get exat result
select distinct a.salary from Salary a where 5=( select count(distinct b.salary) from Salary b where a.salary<=b.salary)
==================================
ok friends tell me which query wil be faster
this query or using top n query
u can mail me at
dolagobinda.sahoo@gmail.com for my feed back
For SQL Server 2005 you have a Windowed function ROW_NUMBER, which you can use as follows:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (order by EMP.SAL DESC) AS RANK FROM EMP) v where RANK = 10;
Is this query work if i am having some employee with the same salary?
Why dont you try it and check?
In continuation to my above comment, I agree that this solution might not give exact result while SAL has duplicate values, but again one can play with the query a little bit. and get desired results.
I required sql query for select top 2 highest paid employee from each department
SELECT *
FROM emp e1
WHERE (1 =
(SELECT COUNT(DISTINCT sal)
FROM emp
WHERE e1.sal <= sal AND e1.deptid = deptid))
UNION
SELECT *
FROM emp e1
WHERE (2 =
(SELECT COUNT(DISTINCT sal)
FROM emp
WHERE e1.sal <= sal AND e1.deptid = deptid))
Hi friends,
select max(sal)from emp e1 where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal);
This query is ‘selecting the n th highest salary’. This query is correct.
I want know how to run this query. please explain this sql query.
this query is working
but i want to display all colunms available on emp table inforomation who r getting 3rd highest salary
Select * from table
where salary in
(
your_query
)
SELECT MIN(sal) FROM tablename
WHERE sal IN
(SELECT TOP 3 sal FROM tablename ORDER BY sal desc)
hai ,
iam just asked that i want to know the maximum salary and minum salary along with the employee details
select * from tablename where sal =(SELECT MIN(sal) FROM tablename
WHERE sal IN
(SELECT TOP 3 sal FROM tablename ORDER BY sal desc))
Hi Prasad,
Hope this will work for you
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ‘sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)
SELECT a.ID1, a.[Name], a.Salary AS Salary
FROM #T1 AS a
WHERE a.Salary IN (
SELECT MIN(b.Salary)
FROM #T1 AS b)
UNION
SELECT a.ID1, a.[Name], a.Salary AS Salary
FROM #T1 AS a
WHERE a.Salary IN (
SELECT MAX(b.Salary)
FROM #T1 AS b)
ORDER BY a.Salary
DROP TABLE #T1
GO
The above query will also give you List of Employees who has Min Salary and List of employees having Max Highest Salary
Vamshi
For getting list of employees with Nth Highest Salary, Here is another approach, Probably this would be similar to one of the above queries
DECLARE @n INT — Variable for accepting N
SELECT @n = 2 — Get the Second Highest Salary
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ‘sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)
SELECT a.ID1, a.[Name], a.Salary
FROM #T1 AS a
WHERE (@n-1) = (
SELECT COUNT(DISTINCT(b.Salary))
FROM #T1 AS b
WHERE b.Salary > a.Salary)
DROP TABLE #T1
GO
Vamshi
This can also be done using DENSE_RANK() function of SQL Server 2005.
DECLARE @n INT — Variable for accepting N
SELECT @n = 2 — Get the Second Highest Salary
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ’sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)
WITH T1 AS
(SELECT (DENSE_RANK() OVER (ORDER BY Salary DESC)) AS RNum, * FROM #T1)
SELECT a.ID1, a.[Name], a.Salary
FROM T1 AS a
WHERE a.RNum = (@n)
Vamshi
Hi guys,
My requirement is like this.
I need four data belonging to four latest audit dates for every business.
I used something like…
SELECT TotalUnitCount FROM @TotalUnitsInLot SecondLatestTotalUnits
WHERE (2-1) =
(SELECT COUNT(DISTINCT(LatestTotalUnits.AuditDate))
FROM @TotalUnitsInLot LatestTotalUnits
WHERE LatestTotalUnits.AuditDate > SecondLatestTotalUnits.AuditDate
AND LatestTotalUnits.BusinessId = SecondLatestTotalUnits.BusinessId
AND LatestTotalUnits.BusinessId = TotalUnits.BusinessId
GROUP BY BusinessId
)
This gives me the results I want, but this is really slow. The problem with
SELECT TOP 1 TotalUnitCount
FROM (
SELECT DISTINCT TOP 4 TotalUnitCount
FROM TotalUnitsInLot
ORDER BY TotalUnitCount DESC) a
ORDER BY TotalUnitCount
approach is, if there are only 3 audits for a business, this query still gets the data from 3rd audit and gives me those results. If there is no data for the 4th audit I want to show TotalUnitCount = 0 for the fourth audit. How do I do that ?