SQL SERVER – Query to Retrieve the Nth Maximum Value | SQL Server Journey with SQL Authority

Feeds:: Posts; Comments

« SQL SERVER – 2005 Locking Hints and Examples

SQL SERVER – Good, Better and Best Programming Techniques »

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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Interview Questions and Answers, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 65 Comments

65 Responses

on April 27, 2007 at 8:28 am | Reply 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 November 25, 2011 at 5:17 am | Reply Manoj Kumar Singh
  
  Hi,
  This is the very nice query
  Regards,
  Manoj Kumar Singh
on April 30, 2007 at 12:37 am | Reply Thanks

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

Hi,

Thanks for the query.

Regards
Manohar K Patidar
on May 29, 2007 at 3:15 am | Reply 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 | Reply Durga Prasad

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

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

Hi,
thanks for the best query.

Best regards…
kishor
on August 3, 2007 at 2:51 pm | Reply 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 | Reply Shaheen

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

Your examples are very useful in my job
on January 17, 2008 at 12:39 pm | Reply 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 | Reply Manjusha

Hi,
Tanks for the perfect n easy solution.

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

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

how to get top 3 departments and bottom 3 departments
on February 27, 2008 at 6:30 am | Reply 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 February 16, 2010 at 4:31 pm | Reply Madhivanan
  
  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
on March 7, 2008 at 7:16 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 April 9, 2013 at 2:48 pm | Reply Vivek
  
  Yeah.. I agree with your approach..
on August 1, 2008 at 10:47 pm | Reply 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 | Reply 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 March 8, 2010 at 8:05 pm | Reply Madhivanan
  
  Then try methods 4 and 5 specified in this link
  
  http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
on September 22, 2008 at 3:58 pm | Reply 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 | Reply manohar

Hi,

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

Regards
Manohar K Patidar Jamner (M. P. )
- on February 16, 2010 at 8:40 pm | Reply Madhivanan
  
  There is no concept of first/last row until you define it
on November 22, 2008 at 8:53 pm | Reply Bharathiraja

table_a table_b

salary salary
_____ _____
1000 2000
3000 4000

I want a T-sql query to get “4000″ as answer
- on February 16, 2010 at 8:41 pm | Reply Madhivanan
  
  select max(salary) as salary from
  (
  select salary from table_a
  union all
  select salary from table_b
  ) as t
on March 21, 2009 at 7:48 am | Reply Vivek

Hello Pinal Dave!!!
I am not able to understand why the following query gave me the correct answer
“SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)”

Can you explain me in step by step manner how it returned correct answer?
I will be extremely thankful to you if you can do it for me.
- on May 11, 2010 at 1:28 pm | Reply Madhivanan
  
  Refer method 3
  http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
  
  It is equalent to this method
  The idea is to generate serial no based on the value and compare it in the where clause
  - on August 26, 2011 at 3:40 pm | Reply sasikumar
    
    in that where clause, u should specify the column name only. Not a variable
on September 27, 2009 at 4:05 pm | Reply killer04

Thanks a lot! This query works well.
on December 4, 2009 at 4:30 pm | Reply manohar K Patidar

How to import by query, as I have below command for table import

exp rtrs_fd/rtrs_fd@reuters file=exp.dmp log=exp.log tables=
issue_counterparty_100 consistent=y
statistics=none buffer=10000000

imp REUT_ONSHORE/REUT_ONSHORE@REUT_US_105 file=C:\IMP\exp.dmp
log=C:\IMP\exp.log tables= issue_source_18_mar09 ignore=y commit=y
buffer=10000000
on December 10, 2009 at 3:50 pm | Reply Suba

hi !! Pinal Dave,

Thanks for the query.. it is really helpful….
but can u explain in steps that how the above query actually works ?
becoz i dont know the execution flow of this query…
on February 11, 2010 at 10:33 am | Reply Archana

hi !! Pinal Dave,

Thanks for the query.. it is really helpful….
but can u explain in steps that how the above query actually works ?
becoz i dont know the execution flow of this query
on February 16, 2010 at 4:28 pm | Reply Madhivanan

There are N number of methods that includes aggregation, row_number(), top etc to get Nth Maximum value

Refer this to know more
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
on March 25, 2010 at 7:52 pm | Reply biswajit

i use sql 2008 and this query gives error .the error shows “Invalid column name ‘N’.” what is error pls tell me
- on May 8, 2010 at 5:19 pm | Reply Dhirendra
  
  hi biswajit,
  
  here N stands for level means which salary level u want to get.
  like n=1,2,3,4,5,6……………n,
  
  try it
  bye
- on May 11, 2010 at 1:30 pm | Reply Madhivanan
  
  You should relplace N with a specific value
  If you use 5, it means 5th maximum
on April 26, 2010 at 11:32 am | Reply saurabh

Hi,

I want to get top 5 salary from employee table without using sql function top and min or max.

Thanks
- on May 11, 2010 at 1:42 pm | Reply Madhivanan
  
  Why do you want to do this?
  The following link has some methods that dont use TOP, min or max
  
  Refer this
  http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
on April 26, 2010 at 4:35 pm | Reply Tejas Shah

Hi Saurabh,

You can use ROWCOUNT property to fetch only five records from query.

SET ROWCOUNT 5;

select * from Employees
ORDER BY Salary DESC

Here I specify ROWCOUNT property to 5, so it will select only 5 records based on query.

By this way you can get top 5 employees having high salary.

Thanks,

Tejas
SQLYoga.com
- on May 11, 2010 at 1:33 pm | Reply Madhivanan
  
  Note that ROWCOUNT will not be supported (for INSERT, UPDATE and DELETE) in future release of the SQL Server. You should use TOP
on May 8, 2010 at 5:14 pm | Reply Dhirendra

hi pinal,

This is dhirendra!
i am quite satisfied by your query.
can u pls breifly give the steps how the query is executed.
waiting 4 ur reply.

thanks
- on May 11, 2010 at 1:45 pm | Reply Madhivanan
  
  It basically generate serial number based on the salary
  It is generated in ascending order of the salary and you use use it in where clause to get Nth maximum value
on May 11, 2010 at 10:12 am | Reply Sambit

Thanks for your query.

Regards,
Sambit
on June 22, 2010 at 7:01 pm | Reply Minakshi

Hello Pinal Dave,

It works for me.Thanks.

Regards,
Minakshi.
on November 17, 2010 at 4:08 pm | Reply Scott Cattell

Hi,

I am looking to retrieve similar data but in a different way but cant quite get it right.

What i have is a customer table and a Purchases table, and i want to create a view that will give me:

Customer, MostRecenetPurchase, SecondMostRecentPurchase,…

Please help!

Many Thanks

Scott
on December 29, 2010 at 7:58 pm | Reply kishan

Hi, can you write a query to find the 5th Highest salary of a table without using inner / sub query.?

Thanks, Kishan
- on December 31, 2010 at 4:09 pm | Reply madhivanan
  
  Make use of this post
  http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
on January 6, 2011 at 12:49 pm | Reply sudheer

Thankq sir
on January 6, 2011 at 12:51 pm | Reply sudheer

thanq sir
on May 6, 2011 at 8:37 am | Reply sagar

Thanks a lot,

I have a query, if I replace “N” with 1, then actually it return lowest and highest one salary.

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

Why?
on November 11, 2011 at 9:44 am | Reply Kalpna Bindal

i want to find the second highest salary from table without ordering table and with no max or count function?
How can i do this???
please reply me…..
thank you..

from Kalpna Bindal
student of 3rd year in B.Tech.
on November 29, 2011 at 12:18 pm | Reply Alok Kumar Ranjan , Bangalore

select e.sal from emp e
where &n = (select count(distinct(b.sal)) from emp b
where e.sal <= b.sal);
on November 30, 2011 at 8:49 pm | Reply divanshu

very nice sir..makes a lot of help..thx.
on July 6, 2012 at 12:10 pm | Reply DK

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.
on July 16, 2012 at 12:28 am | Reply Dinno George

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)
on December 25, 2012 at 4:02 pm | Reply Gajanan

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
- on April 29, 2013 at 12:18 pm | Reply krunal kakadiya
  
  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…..
on April 27, 2013 at 7:01 am | Reply SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

[...] Query to Retrieve the Nth Maximum Value A very popular script on my blog. I am sure you have faced the similar situation in future. [...]
on April 29, 2013 at 12:16 pm | Reply krunal kakadiya

Hello sir,

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

Leave a Reply Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Connecting to %s

Community Initiatives
About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.

Follow @pinaldave
Send +Pinal Dave an email at pinal@sqlauthority.com
- 63,553,654 (63 Million+)
Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 44,170 other followers
SQL in Sixty Seconds
SQL Books

Amazon | 1 | 2 | 3 | 4 | 5
Flipkart | 1 | 2 | 3 | 4 | 5

SQL Interview Q and A
Amazon | Kindle Flipkart
SQL Wait Stats
Amazon | Kindle Flipkart
Funny Index Video
SQLAuthority Links

My Homepage
Windows Live Blog
--------------------
Top Downloads
PDF Downloads
Script Downloads

Script Bank
Favorite Scripts
All Scripts - 1
All Scripts - 2
All Scripts - 3

Top Articles
Best Articles
Favorite Articles - 1
Favorite Articles - 2
--------------------
> SQL Interview Q & A <
SQL Coding Standards
SQL FAQ Download
--------------------
Jobs @ SQLAuthority
About Nupur Dave

Nupur Dave loves technology simply because it makes life more convenient. She is devoted to technology because it touches our heart makes our daily lives easier. Among the many technological programs she uses and embraces Windows Live most because she can do lots of things with ease – from photo management to movies; business emails to personal social media connections.
Top 3 Commenters

  2857 - Madhivanan
  474 - Imran Mohammed
  301 - Ramdas Jaya

Blog at WordPress.com.

Theme: Customized MistyLook by WPThemes.

Send to Email Address Your Name Your Email Address
Cancel
Post was not sent - check your email addresses!

Email check failed, please try again

Sorry, your blog cannot share posts by email.

%d bloggers like this: