SQL SERVER – Query to Retrieve the Nth Maximum Value

Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined. Let us see a query to retrieve the Nth Maximum Value.

SQL SERVER - Query to Retrieve the Nth Maximum Value nthhigh-800x291

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)

In the above example, the inner query uses a value of the outer query in its filter condition, meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row.

In above query where you see (N-1), you can replace the N with any other number based on your need. For example, if you want to find the 10th highest record, you can just write (10-1) or just 9 there and it will bring back necessary record. It is a very easy query and requires one time proper understanding how it works.

Here is another relevant blog post which has working examples of AdventureWorks database: SQL SERVER – Find Nth Highest Record from Database Table

Let me know if you have any question in the comments section. I keep on sharing the various tips, I suggest you sign up for my newsletter http://go.sqlauthority.com

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Joins, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Locking Hints and Examples
Next Post
SQL SERVER – Good, Better and Best Programming Techniques

Related Posts

63 Comments. Leave new

  • Alok Kumar Ranjan , Bangalore
    November 29, 2011 12:18 pm

    select e.sal from emp e
    where &n = (select count(distinct(b.sal)) from emp b
    where e.sal <= b.sal);

    Reply
  • very nice sir..makes a lot of help..thx.

    Reply
  • Hi This query is working fine but whenever there is lacs of records in your table at that time it will take more time so as performance issue i think so my issue will work fine my solution is:
    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
    Here in this query write your table name instead of result , column name instead of maths and your number instead of 3
    Thanks

    Is this helpful? Please give reply.

    Reply
  • Can somebody show me the execution row by row for the Nth max below:

    CREATE TABLE Employee
    (
    EmpId int PRIMARY Key nonclustered,
    Name VARCHAR(50),
    MgrId int,
    Salary int
    );

    sp_help employee
    –drop table employee

    INSERT INTO Employee
    SELECT 1, ‘Mike’, 3, 100
    UNION ALL
    SELECT 2, ‘David’, 3, 200
    UNION ALL
    SELECT 3, ‘Roger’, NULL, 1000
    UNION ALL
    SELECT 4, ‘Marry’,2, 500
    UNION ALL
    SELECT 5, ‘Joseph’,2, 700
    UNION ALL
    SELECT 7, ‘Ben’,2, 50
    GO

    select * from Employee
    order by salary desc

    SELECT * FROM Employee E1
    WHERE (5-1) = (
    SELECT COUNT(DISTINCT(E2.Salary))
    FROM Employee E2
    WHERE E2.Salary > E1.Salary)

    Reply
  • hi, i am getting time out error when i execute the following query

    “select MAX(PartItemID) from PartItems”
    my part item table having 6 lac records
    is there any other way to get the max value for this table ..please help me if u know the solution
    thank u

    Reply
    • krunal kakadiya
      April 29, 2013 12:18 pm

      because it is taking too much time to execute it. when i execute it, it took 6 minutes and 3 seconds and your session expires that’s why it is giving time out error…..

      Reply
  • krunal kakadiya
    April 29, 2013 12:16 pm

    Hello sir,

    your query took 6 minutes and 3 seconds to fetch 9th maximum value..

    Reply
  • I have a employee table there are only 2 fields Name and salary. i want to retrieve 1st record and last record how to retrieve the record please help me…

    Reply

Leave a Reply