SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Find Nth maximum value in SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 04/11/2004 :  19:52:39  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 04/14/2004 :  09:18:13  Show Profile  Reply with Quote
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
Go to Top of Page

ConfusedOfLife
Starting Member

Iran
35 Posts

Posted - 05/09/2004 :  11:09:54  Show Profile  Visit ConfusedOfLife's Homepage  Reply with Quote
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!
Go to Top of Page

gonk
Starting Member

8 Posts

Posted - 05/10/2004 :  10:54:47  Show Profile  Reply with Quote
@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.
Go to Top of Page

harivhn
Starting Member

Singapore
4 Posts

Posted - 06/07/2004 :  05:37:39  Show Profile  Reply with Quote
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

Go to Top of Page

harivhn
Starting Member

Singapore
4 Posts

Posted - 09/07/2004 :  02:09:37  Show Profile  Reply with Quote
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!

Go to Top of Page

velu13
Starting Member

1 Posts

Posted - 04/05/2005 :  00:54:40  Show Profile  Reply with Quote
select min(salary) from employee where salary in
(select distinct top 1 salary from employee order by salary desc)
Go to Top of Page

patilmmilind
Starting Member

1 Posts

Posted - 01/04/2006 :  23:53:30  Show Profile  Reply with Quote
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
Go to Top of Page

vwood
Starting Member

USA
2 Posts

Posted - 10/18/2006 :  13:39:27  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 10/20/2006 :  09:02:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Select min(col) from
(
Select top N col from table order by col DESC
)T

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

selva_tony
Starting Member

India
1 Posts

Posted - 08/08/2007 :  10:55:10  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/09/2007 :  02:51:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

vitta
Starting Member

USA
2 Posts

Posted - 12/16/2007 :  19:55:28  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 12/17/2007 :  03:25:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

vitta
Starting Member

USA
2 Posts

Posted - 11/17/2008 :  20:02:19  Show Profile  Reply with Quote
select x.* from
(
select dense_rank() over(order by salary) as rank,
* from employees
)x where x.rank = n
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000