SQL SERVER – Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

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

,
Previous Post
SQL SERVER – Microsoft SQL Server 2000/2005 Management Pack Download
Next Post
SQLAuthority News – Learn New Things – Self Criticism

Related Posts

338 Comments. Leave new

  • Satinder Mehra
    May 8, 2012 9:39 pm

    .
    .
    . 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
    )

    Reply
  • Satinder Mehra
    May 8, 2012 9:56 pm

    Respected EveryOne

    how can i fetch tha salary between 5 to 10 highest salary from a table ???????

    Reply
    • Looat at point 4 pagination here

      Reply
    • 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

      Reply
    • 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

      Reply
  • “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..

    Reply
  • 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

    Reply
  • Sachin tanwar
    July 3, 2012 2:08 pm

    how one can find the third highest salary ,with using subqueries?

    Reply
    • Refer this for more informations

      Reply
    • 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

      Reply
      • 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

    Reply
  • Sudheer Budati
    July 10, 2012 12:56 pm

    Thanks for your Queries. These r very use full. Really thanks to one and all.

    Regards

    Sudheer.

    Reply
  • select top 1 * from ( select top 7 * from city order by city_id desc) as city_id order by city_id asc

    Reply
  • 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

    Reply
  • 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

    Reply
  • display top 2 salary holder’s from emp table

    Reply
  • Thanks………..Its…..such helpful…

    Reply
  • SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

    Reply
  • select max(sal) from emp where sal<(select max(sal) from emp);

    Reply
  • sir please tell me suppose election polloing application that different categories
    and find out highest votes in one by one categories

    Reply
  • 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

    Reply
  • 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

    Reply
  • (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)

    Reply
  • @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

    Reply
  • 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)

    Reply

Leave a Reply

Menu