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
This is not working for to to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table
Suppose table structure is as follows
EmpID EmpName Salary
1 EMP1 2000
2 EMP2 1000
3 EMP3 5000
4 EMP4 4000
5 EMP5 10000
6 EMP6 250
7 EMP7 100
8 EMP8 2100
Have you tried these methods?
Refer this for more informations
Thanks Pinal,
Your post really helped for fetching nth highest height in SQL Server 2000. I was able to do it in SQL Server 2005 using rowNumber, but was struggling a bit in 2000.
Thanks again !!
Sharad
Refer this for more informations
Some methods would work in all the versions
This is one of the best sites which is not only informative but also exceptionally driven with intent to share knowledge.
Thanks Pinal .
Thanks and Regards
Jaiwanth
e.g. to find out 3rd hieghest salary from table abc.
Select top 1 salary from(select distinct top 3 (salary) from abc order by salary desc)a order by salary asc;
Hi Can anyone tell me what is the function of a in the below query.
SELECT TOP 1 salary
FROM (SELECT TOP 1 salary
FROM employees
ORDER BY salary DESC) a ——–> ?
ORDER BY salary
Query is superb. But i am not understood wat is the use of “a”
i am expecting a quick reply.. Thq.
Hey Pinal, thank you for your blog….
a is the alias name for the derived table
The query
SELECT TOP 1 salary
FROM employees
ORDER BY salary DESC
acts as a derived table
hiii….
plz tell me the sql query for finding that in which departmnet maximum no. of employees are there in employee table of oracle?
This site is for MS SQL Server. For ORALCE questions post at http://www.orafaq.com
plzz answer this question:
in which department employees are getting salary > 1,00,000 lakh
Post table structures, sample data and expected result
plz arrange this in ascending order:–
abc116
abc1115
abc12
abc11113
answer should be:
abc12
abc116
abc1115
abc11113 plz reply me soon..
Refer this post. This will help you. Choose the point that works for you
forget the book
Empsal table data
700
500
100
900
400
200
600
750
query to find second highest salary from table Empsal
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salaryselect distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700
[email addressed removed](amit is back)
Empsal table data
700
500
100
900
400
200
600
750
query to find second highest salary from table Empsal
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salaryselect distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700
query to find second highest salary is
query to find second highest salary from table Empsal
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<(select max(salary) from Empsal));
Output=750
query to find the third highest salary is
forget bookish logic
query to find third highest salary from table Empsal
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700
[email addressed removed](amy is back)
Run query then arg
thank u bro,
it is helpful to a lot
thanks®ards,
sivajee
Hi
If more than one employee getting the same salary means then we can’t get the correct result using Top
You can use TOP WITH TIES
Hello Sir,
I am great fan of your and fact is am new in sql so please help me that where ALIAS a IS USED IN THE QUERY, i know its a silly question.
Select * from
(
select 1 as n
) as t
Here t is the alias for table and n is the alias for column
Find Nth Highest Salary of Employee Without Subquery
Hello Sir,
I am great fan of your and fact is am new in sql so please help me
Find Nth Highest Salary of Employee Without Subquery
Refer this post to know more methods
thank u ,
Thanks®ards,
Brijesh Shah
will you query work if more then one emp share same salary .. i guess no
No. You need to handle it
Hi pinal ,
The Above queries works fine if we need only salary to display.
In case if we want all coloumns then we can try follwing query
select * from t1
where c2 =
(
select max(c2) from t1 where c2 not in
( select top (N-1) c2 from t1 order by c2 desc )
)
Where N is the Nth highest salary required.
The perfromance of this query is really gud as comapre other ones. I have tested this in SQL 2000 and 2005 with 13 lacs records and found that it works much faster then above solution.
This works for duplicate reocords too.
Hi pinal ,
The Above queries works fine if we need only salary to display.
In case if we want all coloumns then we can try follwing query
select * from Employee
where Salary =
(
select max(Salary) from Employee where Salary not in
( select top (N-1) Salary from Employee1 order by Salary desc )
)
Where N is the Nth highest salary required.
The perfromance of this query is really gud as comapre other ones. I have tested this in SQL 2000 and 2005 with 13 lacs records and found that it works much faster then above solution.
This works for duplicate reocords too.
Dear sir ,
I simply fire a simple query “Select top 3 netsal from salaryslip” which gives me 1st three records but i want top 3 highest salary records.
Kindly help me sir
Refer this post