SQL SERVER - Query to Retrieve the Nth Maximum value « Journey to SQL Authority with Pinal Dave

SQL SERVER - Query to Retrieve the Nth Maximum value

April 27, 2007 by pinaldave

Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined.

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.

Source: http://www.sqlteam.com/item.asp?ItemID=16134 (This link does not exist any more)

Posted in SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology | 25 Comments

25 Responses to “SQL SERVER - Query to Retrieve the Nth Maximum value”

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

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.
on April 30, 2007 at 12:37 am Thanks

Wonderful. Thanks. keep on writing.
You are true genius.
on May 11, 2007 at 3:41 am Manohar K Patidar

Hi,

Thanks for the query.

Regards
Manohar K Patidar
on May 29, 2007 at 3:15 am Yogesh D Nayak

Hi

This is a very good query

Keep it up

regards
Yogesh D Nayak
on June 18, 2007 at 4:31 am Durga Prasad

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

Regards,
Durga Prasad A.
on July 30, 2007 at 9:32 pm kishor khilari

Hi,
thanks for the best query.

Best regards…
kishor
on August 3, 2007 at 2:51 pm Bhavesh

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
on September 17, 2007 at 12:47 am Shaheen

Select Top 10 distinct Name,Vlaue from Table1 order by Value desc
on January 11, 2008 at 12:12 pm BalaMurugan.A

Your examples are very useful in my job
on January 17, 2008 at 12:39 pm Vinod Parihar

Dear Sir/Madam,

Thanks for the perfect solution to find out the Nth record from sql server database.
on January 24, 2008 at 3:00 pm Manjusha

Hi,
Tanks for the perfect n easy solution.

Thanks n Regards,
Manjusha
on January 30, 2008 at 6:19 pm manohari

Tks for u r solution
on February 15, 2008 at 6:11 pm uday

how to get top 3 departments and bottom 3 departments
on February 27, 2008 at 6:30 am sekar

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
on March 7, 2008 at 7:16 pm srinath

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
on March 11, 2008 at 11:30 pm SQL SERVER - 2005 - Find Nth Highest Record from Database Table Journey to SQL Authority with Pinal Dave

[...] 8, 2008 by pinaldave I have previously written SQL SERVER - Query to Retrieve the Nth Maximum value. I just received email that if I can write this using AdventureWorks database as it is default [...]
on April 6, 2008 at 2:06 pm SQL SERVER - Find Nth Highest Salary of Employee - Query to Retrieve the Nth Maximum value Journey to SQL Authority with Pinal Dave

[...] Please read my article here to find Nth Highest Salary of Employee table : SQL SERVER - Query to Retrieve the Nth Maximum value [...]
on April 17, 2008 at 5:48 pm Kapil Madan

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
on July 30, 2008 at 11:20 am Krishna

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
on July 31, 2008 at 3:00 pm MilesPerSecond

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 !!!!
on August 1, 2008 at 10:47 pm Bala

Hello sir,
i have one dpubt……..
How will we identify the duplicate key by refer the number?
on September 7, 2008 at 1:56 pm lawender

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.
on September 22, 2008 at 3:58 pm Satyabrata Paul

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
on September 26, 2008 at 12:00 pm manohar

Hi,

Can we find out the first inserted row from a table?

Regards
Manohar K Patidar Jamner (M. P. )
on November 22, 2008 at 8:53 pm Bharathiraja

table_a table_b

salary salary
_____ _____
1000 2000
3000 4000

I want a T-sql query to get “4000″ as answer