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
‘,sp_tables –‘
hi this is srinu,
i have visited ur site multiple times,
really ur publishing valuable information to beginners like me.
in this query u used a
SELECT TOP 1 Sal
FROM (
SELECT DISTINCT TOP 2 Sal
FROM Emp
ORDER BY Sal DESC) a
ORDER BY Sal
why did u use that a variable can u explain me..
thanks in advance
regards
Srinu Ganaparthi
@Srinu,
Which variable you are talking about, I dont see any variable.
Please post your complete question.
~IM.
Hi Sinu,
To consider that whole query as single table we need to use that variable..even you can use whatever the variable you like not only a..u can use b..whatever u like
hope it will give u good idea on that.
@ Srinu
No need to confuse with the ‘ a ‘ . Its just a alias name given for the table particularly in the case of sub queries. Be aware that we written a sub query to achieve the result.
If u didn’t specify the name in SQL Server 2000/2005 , it wont give any effect to the result but just observe that when u execute the query system will automatically attach table name_1 alias name to the table.
I hope u got somewhat clear idea.
Regards,
Raj
@ Srinu ,
Just check this query . It will give 4th highest salary from the list of employee salaries.
SELECT MIN(empsal) AS Empsalary
FROM emp
WHERE (empsal IN
(SELECT DISTINCT TOP (4) empsal
FROM emp AS emp_1
ORDER BY empsal DESC))
notice that emp_1 is created while executing the query. Its automatically generated one.
@ All
Thanks guys … for valuable posts … keep posting … its a good sign for us … we can share and solve others problems too….
regards,
Raj
i want to retrive nth max sal from emp……….this query gvn eroor please solve it and tell me
SQL> SELECT TOP 1 sal
2 FROM (
3 SELECT DISTINCT TOP 6 sal
4 FROM emp
5 ORDER BY sal DESC) a
6 ORDER BY sal;
SELECT TOP 1 sal
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> SELECT sal
2 FROM (
3 SELECT DISTINCT TOP 6 sal
4 FROM emp
5 ORDER BY sal DESC) a
6 ORDER BY sal;
SELECT DISTINCT TOP 6 sal
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected
The way to find nth ranking, is with RANK().
SELECT sal FROM
(SELECT sal, RANK(ORDER BY sal DESC) rn)
WHERE rn = rank-you-want
This will report all people at that level. If, however, you want one and only one, ROW_NUMBER():
SELECT sal FROM
(SELECT sal, ROW_NUMBER() OVER(ORDER BY sal DESC) rn)
WHERE rn = rank-you-want
Please explain the following query as in how it is interperated.
SELECT *
FROM emp A
WHERE (n-1) = (
SELECT COUNT(DISTINCT(B.salary))
FROM emp B
WHERE B.salary > A.salary)
Hi, Pinal.
It is a great article on finding the nth highest value in a column.
I have a dummy table emp(emp_name,deptname,salary)
and i want to find out the second highest salary department wise.
would you please help me to sort it out ?
anyone’s help will be greatly appreciated..
Thanks and regards
Deba
@debadutta..
u can work on this…
select max(e1.sal), e1.deptno from emp e1
where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal)
group by deptno;
u might get ur solution….i supose…just work on this
Thanks
so good answer
superb…
thankx..
very goood.
@debadutta..
u can work on this…
select max(e1.sal), e1.deptno from emp e1
where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal)
group by deptno;
u might get ur solution….i supose…just work on this
select max(e1.sal), e1.deptno from emp e1
where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal)
group by deptno;
can u explain this query how its work
explain where condiation also ????
@Mookkandi @sharad
Here’s an explanation of how the following query works:
For each row, this query makes a cartesian product with other rows of the table. Then according to the inner query syntax, it compares the salaries. Then, the number comparison in the first outer query makes sure that only those records are picked which satisfy the criteria. E.g.
Let’s say u got 3 records, with salaries 10, 20 and 30. So, the cartesian product is something like,
1) For row with sal 10 –> 10-10, 10-20, 10-30
2) For row with sal 20 –> 20-10, 20-20, 20-30
3) For row with sal 30 –> 30-10, 30-20, 30-30
Now apply the condition, e1.sal 10-10, 10-20, 10-30 (3)
2) For row with sal 20 –> 20-10, 20-20, 20-30 (2)
3) For row with sal 30 –> 30-10, 30-20, 30-30 (1)
Now the outer condition, 2<= (the count arrived at above). So only 2) and 3) get selected. Hence, it shows salaries according to outer query for these two i.e. 20 and 30.
PS: This is inefficient and should be avoided. Prefer rank() in Oracle
Hope it helps.
This query has good execution plan among all the queries explained here .
Select *
from (Select *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Ranks from #T1) a
WHERE Ranks = N
Thanks,
-Amol.
Hey Pinal,
Good Blog. But just wanted to let you know that there should be a very minute change required in this query:
USE AdventureWorks;
GO
SELECT TOP 1 Rate
FROM (
SELECT DISTINCT TOP 4 Rate
FROM HumanResources.EmployeePayHistory
ORDER BY Rate DESC) A
ORDER BY Rate DESC –Desc should be there in order to get top record
GO
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
enter n value.if u want 3rd max salary place n value 2.
Thank guy…….
i have question in oracle
how can i find the second highest manager who has second highest number of employees reporting to him.
say for eg:
if mgr 789 has 3 employees reporting to him
and mgr 889 has 5 employees reporting to him and
mgr 779 has 2 employees reporting to him
i need to get mgr 789 as my answer as he has the second highest employees reporting to him
i need to find out this using sql query.
pls help