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
.
.
. 2nd highest value ????????????????
select max(salary) as ‘second highest salary’ from tbemployee where salary not in
(
select top 2 (salary) from tbemployee order by salary desc
)
Respected EveryOne
how can i fetch tha salary between 5 to 10 highest salary from a table ???????
Looat at point 4 pagination here
Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)
INSERT INTO #EMP
SELECT 1,’VINAY’,15000.00 UNION
SELECT 2,’KESAV’,52890.00 UNION
SELECT 3,’HANU’,24000.00 UNION
SELECT 4,’ARUN’,42200.00 UNION
SELECT 5,’KISHORE’,25000.00 UNION
SELECT 6,’VINOD’,32000.00 UNION
SELECT 7,’SENDIL’,22890.00 UNION
SELECT 8,’RAKESH’,26400.00 UNION
SELECT 9,’SURYA’,25200.00 UNION
SELECT 10,’SIVA’,28900.00
SELECT * FROM #EMP
SELECT * FROM #EMP ORDER BY SAL DESC
–TO GET ONLY TOP 5-10 HIGHEST SALARIES
SELECT TOP 5 SAL FROM #EMP WHERE ENO NOT IN
(SELECT TOP 5 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC
–TO GET ONLY TOP 5-10 HIGHEST SALARIED EMPLOYEE DETAILS
SELECT TOP 5 * FROM #EMP WHERE ENO NOT IN
(SELECT TOP 5 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC
DROP TABLE #EMP
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RN,* FROM #EMP) AS T WHERE T.RN BETWEEN 6 and 10
–also you can use
“select max(salary) from (select distinct top 5 salary from Emp_Id order by salary desc) a “…why we have used ‘a’ after the subquery..will anyone pls tell me..
To make the further operations over the outer query (like) where etc it is use full
It is a alias name given to the derived table
Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)
INSERT INTO #EMP
SELECT 1,’VINAY’,15000.00 UNION
SELECT 2,’KESAV’,52890.00 UNION
SELECT 3,’HANU’,24000.00 UNION
SELECT 4,’ARUN’,42200.00 UNION
SELECT 5,’KISHORE’,25000.00
SELECT * FROM #EMP
SELECT TOP 1 SAL FROM #EMP WHERE ENO NOT IN
(SELECT TOP 1 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC– To get only 2nd highest salary
SELECT TOP 1 * FROM #EMP WHERE ENO NOT IN
(SELECT TOP 1 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC– To get the details of 2nd highest salaried emploee details
DROP TABLE #EMP
how one can find the third highest salary ,with using subqueries?
Refer this for more informations
SELECT TOP 1 * FROM #EMP WHERE ENO NOT IN
(SELECT TOP 2 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC– To get the details of 2nd highest salaried emploee details
even using Row_Number you can do this
SELECT ENO,ENAME,SAL FROM
(SELECT Row_Number() OVER (ORDER BY SAL DESC) AS RN,*
FROM #EMP) E WHERE E.RN =3
– To get the details of 3nd highest salaried emploee details
Hi Purna,
Your answer will not work when there is multiple record with same sal
for example:
if there is records like:
ENO ENAME SAL
1 ABC 3000
2 XYZ 3000
3 PQR 2000
4 DEF 1000
This time it will give record 3 as answer
I have solution of it:
with result AS
(
select maths,RANK() over (order by maths desc) as row from tblResult group by maths
)
select * from result r inner join tblResult t on r.maths = t.Maths where ROW = 3
in this query instead of result write your tablename instead of maths write your column name instead of 3 write your number which you want
Thanks
Give your response is this helpful or not
Hi Mr.DK,
Absolutely correct. But I’ll update my query now to this
Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)
INSERT INTO #EMP
SELECT 1,’VINAY’,3000.00 UNION
SELECT 2,’KESAV’,3000.00 UNION
SELECT 3,’HANU’,2000.00 UNION
SELECT 4,’ARUN’,2000.00 UNION
SELECT 5,’Sanood’,5000.00 UNION
SELECT 6,’Vijay’,1000.00
SELECT ENO,ENAME,SAL FROM
(SELECT Dense_Rank() OVER (ORDER BY SAL DESC) AS RN,* FROM #EMP) E WHERE E.RN =3
Even this will work perfectly then Rank() function when there is multiple values with same Sal then Rank will escape some values
result:
ENO ENAME SAL
3 HANU 2000
4 ARUN 2000
Hi your query is right giving correct i have another solution
with result AS
(
select maths,RANK() over (order by maths desc) as row from tblResult group by maths
)
select * from result r inner join tblResult t on r.maths = t.Maths where ROW = 3
in this query instead of result write your tablename instead of maths write your column name instead of 3 write your number which you want
Thanks
Thanks for your Queries. These r very use full. Really thanks to one and all.
Regards
Sudheer.
select top 1 * from ( select top 7 * from city order by city_id desc) as city_id order by city_id asc
Q1. SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 6 salary FROM employee
ORDER BY salary DESC) a
ORDER BY salary
what does this ‘ a ‘ means here.
Q2. How to auto number the rows in a table in Oracle SQL*9 ?
Q3. How to execute the query to find the 3rd highest salary of a employee query in Oracle SQL*9 ?
Q3. Write a sql query to display employee name with grade name as a column. If a employee having salary greater than or equal to 5000 than the grade = GOLD otherwise SILVER ?
Hint : u have a Employee(empid,empname,salary) table
Please HELP ASAP!!!
—
Thanks in advance!
Vijesh Jain
you can do it by below query as well
suppose i have to find out the fifth highest salary of an employee
then
select Salary from
(select *,DENSE_RANK() over (order by Salary desc) as ‘num’ from tblEmployee) t1
where num=5
it will return the fifth highest salary of an employee
display top 2 salary holder’s from emp table
Thanks………..Its…..such helpful…
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)
select max(sal) from emp where sal<(select max(sal) from emp);
sir please tell me suppose election polloing application that different categories
and find out highest votes in one by one categories
select top 1 salary
from ( select distinct top 2 salary
from Salary order by salary desc)a order by salary what is meaning of a after from () a order by salary
SELECT MIN (SALARY )
FROM UEXAMPLE1
WHERE SALARY IN (SELECT DISTINCT TOP 4 SALARY FROM UEXAMPLE1 ORDER BY SALARY DESC),
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
both the queries are not woking in oracle it gives the error FROM keyword not found where expected
(TableName=Student, ColumnName=Mark) :=>
select *from student where mark=(select mark from(select row_number() over (order by mark desc) as t,mark from student group by mark) as td where t=2)
@Pinal
Thanks for different way of writing query but how would you find 2nd highest salary of employees, if 2 or more employees having same salary which is second highest salary
Please let me know
Thanks,
Amol P
select MIN(STUDENT_ID) from TBL_STUDENT where STUDENT_ID in( select distinct top N student_id from TBL_STUDENT order by STUDENT_ID desc)