Article: Find Nth maximum value in SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Find Nth maximum value in SQL Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-11 : 19:52:39

This aritlce is written by Hariharan Velayuthan. He writes "There are several methods to find out the Nth maximum/minimum value using SQL. This article discusses on such method to find Nth maximum value from a desired table. This article is aimed at users who are in the beginner or intermediate level in SQL Server."

Article Link.

gonk
Starting Member

8 Posts

Posted - 2004-04-14 : 09:18:13

hi hariharan,

i tried this on a table with 100k rows. like you told us, its very ressource-consuming. took about 20 minutes to complete. well your solution looks smart. but i think you can do the job much more effective with a simple subselect:

SELECT TOP 1 *
FROM (SELECT TOP 4 *
FROM employee
ORDER BY salary) e2
ORDER BY Salary DESC

what do you think ?

chris

ConfusedOfLife
Starting Member

35 Posts

Posted - 2004-05-09 : 11:09:54

Why don't you simply use an ORDER BY clause?! Don't you think that sometimes you should leave some tasks to the programming language you use? It's no need that you do everything by hand! You can get the the Nth record by doing a LIMIT Nth and ORDER BY field DESC, so, what's wrong with it? Then you can retrieve the data by whatever programming language you use. I THINK YOU'LL NEVER NEED TO GET THIS VALUE BY THE HELP OF SQL in the real world!

gonk
Starting Member

8 Posts

Posted - 2004-05-10 : 10:54:47

@col well you are maybe right, but that it is not the point, if it makes sense to retrieve the nth value via sql or not. many people ask for this feature and asksqlteam tried to answear it. in my opinion their solution is bad and won't work fats enough so i provided a better solution.

harivhn
Starting Member

4 Posts

Posted - 2004-06-07 : 05:37:39

Hi Chris,

Sorry for the late response, I was on a vacation. You must have noticed in my article, I had a poin on the performance overhead caused by correlated subqueries. Just in case you have forgotten, read the lines below from my article
-
As a "Rule of Thumb" keep these points in mind, when you use a correlated sub-query

Correlated sub-query is a performance overhead to the database server and so, you have to use it only if it is required
Avoid using Correlated subquery on large tables, as the inner query is evaluated for each row of the outer query
-

I had never mentioned in my article that there is NO other option to find out N-th maximum. The objective of my article was to find out N-th maximum value using correlated subquery and moreover my query WORKS IN ALMOST ALL THE VERSIONS OF SQL SERVER AND EVEN IN ORACLE coz it uses pure Structure Query Langaugae standards.

Anyway thanks for your comments.

Hari

quote:
Originally posted by gonk

hi hariharan,

i tried this on a table with 100k rows. like you told us, its very ressource-consuming. took about 20 minutes to complete. well your solution looks smart. but i think you can do the job much more effective with a simple subselect:

SELECT TOP 1 *
FROM (SELECT TOP 4 *
FROM employee
ORDER BY salary) e2
ORDER BY Salary DESC

what do you think ?

chris

harivhn
Starting Member

4 Posts

Posted - 2004-09-07 : 02:09:37

Hello Mr. ConfusedOfLife,

My article was not intended to find the Nth maximum, but was to explain the technicalities of corelated subqueries. Am pretty much aware that we can handle such things using "select TOP N..." with Order by clause.

YOU HAVE TO REALLY UNDERSTAND THE OBJECTIVE OF THE ARTICLE BEFORE MAKING SUCH COMMENTS.

Hari

quote:
Originally posted by ConfusedOfLife

Why don't you simply use an ORDER BY clause?! Don't you think that sometimes you should leave some tasks to the programming language you use? It's no need that you do everything by hand! You can get the the Nth record by doing a LIMIT Nth and ORDER BY field DESC, so, what's wrong with it? Then you can retrieve the data by whatever programming language you use. I THINK YOU'LL NEVER NEED TO GET THIS VALUE BY THE HELP OF SQL in the real world!

velu13
Starting Member

1 Post

Posted - 2005-04-05 : 00:54:40

select min(salary) from employee where salary in
(select distinct top 1 salary from employee order by salary desc)

patilmmilind
Starting Member

1 Post

Posted - 2006-01-04 : 23:53:30

Hello everybody ,
Here is a logic for finding nth maximum

Select Constder "Student" table with "Student_Class" Field

And the query will be as follows :-

Select Top 1 Student_Class From (Select Top 2 Student_Class
From Student Order By Student_Class Desc) a Order By Student_Class
Asc

vwood
Starting Member

2 Posts

Posted - 2006-10-18 : 13:39:27

I don't know if thunderstone moves their nodes around or not, but the corellated subquery reference is currently node98 (not node 90).
http://www.thunderstone.com/site/texisman/node98.html

For my applications, there's a few problems with TOP n:
* The TOP parameter must be a hardcoded constant (TOP 10). You cannot use a variable (TOP @n).
* You can't get the TOP 4 employees for each salary level, like a GROUP BY. That's a silly example, but here's a variant that I'm trying to do: for each date, I want to find the previous [n] data dates (not calendar dates). The solutions using TOP would get the top of all dates, the correlated subquery can get the top dates that are less than each date in the parent query.
* The correlated subquery solution will return two records in the case of a tie. In the "Employees" example, his returns both Jack & John. The TOP alternatives presented will arbitrarily return either Jack or John. Which way is better depends on your application, but generally I prefer SQL to present all records that fit the criterion, and hate letting a computer make arbitrary decisions.

Mr. Velayuthan presents a useful technique, and discloses the limitations. It is true that when I use correllated subqueries this way, it takes a very long time. On the other hand, this technique is pure SQL and fits an OOP programming model that I think is easier to maintain.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-20 : 09:02:46

Select min(col) from
(
Select top N col from table order by col DESC
)T

Madhivanan

Failing to plan is Planning to fail

selva_tony
Starting Member

1 Post

Posted - 2007-08-08 : 10:55:10

quote:
Originally posted by madhivanan

Select min(col) from
(
Select top N col from table order by col DESC
)T

Madhivanan

Failing to plan is Planning to fail

Cool...
Excellent query mathi....
I like it....
It's much optimized...
Really Cooooool query...

Antony S

love others and be loved

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-09 : 02:51:02

quote:
Originally posted by selva_tony

quote:
Originally posted by madhivanan

Select min(col) from
(
Select top N col from table order by col DESC
)T

Madhivanan

Failing to plan is Planning to fail

Cool...
Excellent query mathi....
I like it....
It's much optimized...
Really Cooooool query...

Antony S

love others and be loved

Thanks. You are welcome

Madhivanan

Failing to plan is Planning to fail

vitta
Starting Member

2 Posts

Posted - 2007-12-16 : 19:55:28

same query using row_number() with sql 2005

SELECT T.* FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROW_ID
,NAME
,SALARY
FROM EMPLOYEE
)T WHERE T.ROW_ID = N

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-17 : 03:25:44

Some more methods
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

Madhivanan

Failing to plan is Planning to fail

vitta
Starting Member

2 Posts

Posted - 2008-11-17 : 20:02:19

select x.* from
(
select dense_rank() over(order by salary) as rank,
* from employees
)x where x.rank = n

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources