This question is quite a popular question and it is interesting that I have been receiving this question every other day. Here is how to find the Nth Highest Salary of Employee, with the short answer first and the older methods after it.
The answer I would give in an interview
DENSE_RANK numbers the salaries from the top, and gives equal salaries the same number. Ask for number 6 and you get the sixth highest salary, however many people are on it.
WITH RankedSalary AS
(
SELECT Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employee
)
SELECT DISTINCT Salary
FROM RankedSalary
WHERE SalaryRank = 6;Change the 6 to whatever you need. That is the whole answer, and it reads the table once.
Why DENSE_RANK and not RANK or ROW_NUMBER
This is the part interviewers are really asking about, so it is worth being clear. Say five people earn 90,000 and they are the top earners.
ROW_NUMBER gives them 1, 2, 3, 4, 5. The next salary down is 6. So “the second highest salary” comes back as 90,000 again, which is wrong.
RANK gives all five of them 1, then jumps to 6. Ask for rank 2 and you get nothing at all.
DENSE_RANK gives all five of them 1, then the next salary down is 2. That matches what people mean by “the second highest salary”.
If what you actually want is the sixth highest paid person rather than the sixth highest salary, then ROW_NUMBER is the right one. Decide which question you are answering before you pick.
The short version with OFFSET
From SQL Server 2012 onwards there is a neater way when duplicates are not a worry.
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC OFFSET 5 ROWS FETCH NEXT 1 ROWS ONLY;
Skip five, take one, and you have the sixth. Note it is OFFSET 5 for the sixth value, which catches people out. The DISTINCT matters here, because without it you are counting people rather than salaries.
The older method, and it still works
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. 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
Read it from the inside out. The inner query takes the top six distinct salaries. The outer query sorts those six the other way up and takes the first, which is the smallest of the six, which is the sixth highest. It is a neat trick and it works on any version of SQL Server.
You can change and use it for getting nth highest salary from Employee table as follows, where n is always greater than one.
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary
Two things to know before you use it. It cannot do n = 1, as the note above says, so your code needs a special case for the highest salary. And TOP will not take a variable directly, so for a real n you need dynamic SQL or TOP (@n) with the brackets. The DENSE_RANK version has neither problem, which is why I lead with it now.
Same example converted 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
Highest salary per department
The follow up question, every time. Add PARTITION BY and the same query answers it per department without any extra work.
WITH RankedSalary AS
(
SELECT DepartmentID,
Salary,
DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
FROM Employee
)
SELECT DepartmentID, Salary
FROM RankedSalary
WHERE SalaryRank = 2
ORDER BY DepartmentID;PARTITION BY restarts the numbering for each department. That one line is the difference between a query that answers the interview question and one that answers the real question.
I have answered this question before as well, and those posts are still here: Query to Retrieve the Nth Maximum value and Find Nth Highest Record from Database Table.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
338 Comments. Leave new
Hi all,
Help me to write an sql query ..
suppose i have 4 departments. I need max salary in department(1) and 2nd highest salary in department(2) and third highest in dept(3) and least sal in 4th dept.
my table is like this
name sal dept
A 1000 1
B 1600 1
C 950 1
D 1050 1
E 960 2
F 800 2
G 840 2
H 1010 2
I 999 3
J 970 3
K 1000 3
L 940 3
M 1200 4
N 1050 4
P 888 4
R 799 4
I WANT THE OUTPUT LIKE THIS:
NAME SAL DEPT
B 1600 1
E 960 2 — SECOND HIGHEST IN DEPT
J 970 3 — THIRD HIGHEST
R 799 4 — 4TH HIGHEST
Is it possible to do it in SQL or we have to go for PLSQL. Please reply..
thanks in advance….
SELECT name, salary from emp order by salary desc limit 0, 1
This is specific to MySQL. But this site is for SQL Server
select ename,sal from (select ename,sal from emp order by sal desc) where rownum<=1;
Second Last Greater Number
SELECT GrandTotal FROM
( SELECT GrandTotal,ROW_NUMBER() OVER(ORDER BY GrandTotal desc) As RowNum
FROM def_ServiceProvider ) As A
WHERE A.RowNum IN (2,3)
Consider you want the second highest salary.
———————————————–
DECLARE @nth int;
SET @nth=2;
———————————————–
This example returns the nth highest salary from the employee table if the nth highest salary exists; else it returns -1.
———————————————–
SELECT (CASE WHEN COUNT(*)=@nth THEN MIN(salary) ELSE -1 END) AS salary
FROM
(
SELECT DISTINCT TOP (@nth) salary
FROM Employee
ORDER BY salary DESC
) a
———————————————–
NOTE: If you want to return NULL, replace -1 for NULL.
how we will run this query “Select min(Employee_Salary) from Employee_Test where
Employee_Salary IN(Select Distinct top 2 Employee_Salary from Employee_Test order by Employee_Salary desc );” in my sql as my sql does not support top keyword
best ewample yar
Hi PINAL DAVE
what is “a” in query.
its an Alias name.
can someone let me the query to retrieve the top 5 salary for a group of people. the out put should bee 5 records. For example in a 100 records, if 5 users have top 5 salaries.
/*for 3rd highest salary n=2, for 2nd highest salary n=1, nth highest salary n=n-1*/
SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT n,1
Hi Pinal
Select DISTINCT A.CSTCOD,A.PONUMB,Rank() OVER (PARTITION by ITMCOD ORDER BY PODATE,RATAMT,A.PONUMB) AS Rnk,
PODATE,ITMCOD,ITMNAM,PURUOM,PURQTY,RATAMT,VALAMT From INPOBTBL A
Inner Join
(Select CSTCOD,PONUMB, PODATE From INPOHTBL Where CSTCOD=13 and PODATE between 20140201 and 20140228 ) B On
A.CSTCOD=b.CSTCOD And A.PONUMB=B.PONUMB
WHERE ITMCOD in (20018,20007)
From the above query out put i need to get only the record which is having the max of RNK Column . first thing i wanted to know is my approach is correct if yes how can i get the row where Rank is Max when am doing OVER (PARTITION by ITMCOD ORDER BY PODATE,RATAMT,A.PONUMB).
Thank you
Hi! Can you update a general sql query to find the nth highest or lowest in each group without using any analytics function or TOP
For Ex- Find the nth highest salary within each department . Without using ROW_NUMBER or ROWNUM or TOP or such functions or pseudo columns
emp table have emp details exept sal,sal table have sal how to find the 2 nd highest sa
Thrilok, your question is not clear to me.
selct person name form person where income <100000 and area name person name
Mayur – your question is not clear to me.
Sir i have one question with you. How we can get maximum salary of employee in given table. We dont need one. We need result of those persons who are getting maximum.
TableName: EMP_MGR
Emp_ID Emp_Name Salary Mgr_ID
1 Aman 45000 NULL
2 Deepak 35000 1
3 Pankaj 32000 1
4 Sapna 25000 1
5 Rajan 50000 NULL
6 Nupur 18000 5
7 Anamika 18000 5
8 Preet 22000 NULL
9 Shalu 27000 NULL
10 Jyoti 12000 9
11 Omesh 25000 9
12 Rakesh 21000 9
SELECT MAX(salary),E1.mgr_id FROM EMP_MGR E1,
(SELECT MAX(salary) AS sal,mgr_id FROM EMP_MGR GROUP BY mgr_id ) E2
WHERE E1.salary<E2.sal and E1.mgr_id = E2.mgr_id GROUP BY E1.mgr_id;
I want to find 2nd Highest record from each group including Managers(NULL group)
Expected Result:
Mgr_ID Salary
NULL 45000
1 32000
5 18000
9 15000
SELECT salary FROM employee ORDER BY salary DESC LIMIT 1
what is ‘a’ means in the query i don’t understand it
How to fetch the nth highest salary from a table without using TOP and sub-query?
IF you use using version 2005 or later, try using row_number() function
select t1.* from(
select *,row_number() over (order by salary desc) as sno from table
) as t
where sno=1