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

  • 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….

    Reply
  • SELECT name, salary from emp order by salary desc limit 0, 1

    Reply
  • select ename,sal from (select ename,sal from emp order by sal desc) where rownum<=1;

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

    Reply
  • Carlos Oliveira
    May 31, 2013 10:33 am

    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.

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

    Reply
  • best ewample yar

    Reply
  • Hi PINAL DAVE

    what is “a” in query.

    Reply
  • Sureshkumar Miriyala
    January 9, 2014 3:57 pm

    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.

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

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

    Reply
  • Sridhar Mahendrakar
    June 9, 2014 3:59 pm

    Thank you

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

    Reply
  • emp table have emp details exept sal,sal table have sal how to find the 2 nd highest sa

    Reply
  • selct person name form person where income <100000 and area name person name

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

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

    Reply
  • SELECT salary FROM employee ORDER BY salary DESC LIMIT 1

    Reply
  • aditya rawat
    May 27, 2016 12:56 pm

    what is ‘a’ means in the query i don’t understand it

    Reply
  • How to fetch the nth highest salary from a table without using TOP and sub-query?

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

      Reply

Leave a Reply

Menu