June 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

SQL SERVER – Query to Retrieve the Nth Maximum Value

Pinal Dave
SQL Tips and Tricks
63 Comments

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

  • Nicholas Paldino [.NET/C# MVP]
    April 27, 2007 8:28 am

    It should be noted that for SQL Server 2005, using the row_number ranking function is a better, meaning easier to maintain, and more performant.

    Reply
  • Thanks
    April 30, 2007 12:37 am

    Wonderful. Thanks. keep on writing.
    You are true genius.

    Reply
  • Manohar K Patidar
    May 11, 2007 3:41 am

    Hi,

    Thanks for the query.

    Regards
    Manohar K Patidar

    Reply
  • Yogesh D Nayak
    May 29, 2007 3:15 am

    Hi

    This is a very good query

    Keep it up

    regards
    Yogesh D Nayak

    Reply
  • Durga Prasad
    June 18, 2007 4:31 am

    I got the correct solution from your website. thank u.

    Regards,
    Durga Prasad A.

    Reply
  • kishor khilari
    July 30, 2007 9:32 pm

    Hi,
    thanks for the best query.

    Best regards…
    kishor

    Reply
  • Bhavesh
    August 3, 2007 2:51 pm

    Hello,

    I want to find first 10 rows with max value from a table. Can you suggest me what should be query for this ?

    Thanking you in advance.

    Regds,
    Bhavesh

    Reply
  • Shaheen
    September 17, 2007 12:47 am

    Select Top 10 distinct Name,Vlaue from Table1 order by Value desc

    Reply
  • BalaMurugan.A
    January 11, 2008 12:12 pm

    Your examples are very useful in my job

    Reply
  • Vinod Parihar
    January 17, 2008 12:39 pm

    Dear Sir/Madam,

    Thanks for the perfect solution to find out the Nth record from sql server database.

    Reply
  • Manjusha
    January 24, 2008 3:00 pm

    Hi,
    Tanks for the perfect n easy solution.

    Thanks n Regards,
    Manjusha

    Reply
  • manohari
    January 30, 2008 6:19 pm

    Tks for u r solution

    Reply
  • sekar
    February 27, 2008 6:30 am

    Hi,

    Thanks for the perfect solution to find out the Nth record from sql server database.

    One request:
    how to get top 3 departments and bottom 3 departments

    waiting for ur reply

    with reagards
    sekar

    Reply
    • Madhivanan
      February 16, 2010 4:31 pm

      Top 3

      Select top 3 * from table order by dept desc

      Bottom 3

      Select top 3 * from table order by dept asc

      But you need to define what you meant by Top and bottom

      Reply
  • srinath
    March 7, 2008 7:16 pm

    hi

    i want a query .dynamicale i new row should Added and it should display in Gui by using a Query.pleas provide me this query.

    Regards
    Srinath

    Reply
  • Kapil Madan
    April 17, 2008 5:48 pm

    Hi executing the same query using Top keyword is much faster, when the number of records are comparatively too large. FYI (copied from the same site :) )

    SELECT TOP 1 salary
    FROM (
    SELECT DISTINCT TOP 6 salary
    FROM employee
    ORDER BY salary DESC) a
    ORDER BY salary

    Reply
  • Krishna
    July 30, 2008 11:20 am

    I want to find first 10 rows with max value from a table.
    and

    how to get top 3 departments and bottom 3 departments
    Can you suggest me what should be queries for above two questions?

    Thanks
    krishna

    Reply
  • MilesPerSecond
    July 31, 2008 3:00 pm

    Hi All,
    I compared above said query and found this one performming like a Lamborghini Reventon as compared to the above proposed Ambassador, for table(id,name,salary) with 20K records:

    DECLARE @myNth INT
    SET @myNth=56
    SELECT TOP 1 SALARY
    FROM(
    SELECT TOP(@myNth) SALARY
    FROM MinMaxTable
    ORDER BY Salary DESC
    ) T
    ORDER BY Salary ASC

    So This one is the BEST !!!!

    Reply
  • Bala
    August 1, 2008 10:47 pm

    Hello sir,
    i have one dpubt……..
    How will we identify the duplicate key by refer the number?

    Reply
  • lawender
    September 7, 2008 1:56 pm

    Your query is correct if it came from student who has 5 minutes to solve this problem.

    But if it in real world application,
    Your query is very low performance.

    Reply
  • Satyabrata Paul
    September 22, 2008 3:58 pm

    Hi

    following query will find out the nth max salary from employee table. just replace the number 5 with your number. Also it should check the duplicate value in salary field. I think Bala wants to know this.

    SELECT max(salary) FROM ( SELECT DISTINCT TOP 5 salary FROM employee order by salary) a

    Regards
    Satyabrata Paul

    Reply
Newer Comments »

Leave a Reply

Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.

Pinal has authored 14 SQL Server database books and 90 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.

Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.

Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).

Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.

Comprehensive Database Performance Health Check

Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.

Once you learn my business secrets, you will fix the majority of problems in the future.

SQL Server Performance Tuning Practical Workshop

Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.

Essentially I share my business secrets to optimize SQL Server performance.