| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
gonk
Starting Member
8 Posts |
 Posted - 04/14/2004 : 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
Iran
35 Posts |
Posted - 05/09/2004 : 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 - 05/10/2004 : 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
Singapore
4 Posts |
Posted - 06/07/2004 : 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
Singapore
4 Posts |
Posted - 09/07/2004 : 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 Posts |
Posted - 04/05/2005 : 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 Posts |
Posted - 01/04/2006 : 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
USA
2 Posts |
Posted - 10/18/2006 : 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
India
22461 Posts |
Posted - 10/20/2006 : 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
India
1 Posts |
Posted - 08/08/2007 : 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
India
22461 Posts |
Posted - 08/09/2007 : 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
USA
2 Posts |
Posted - 12/16/2007 : 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
India
22461 Posts |
|
|
vitta
Starting Member
USA
2 Posts |
Posted - 11/17/2008 : 20:02:19
|
select x.* from
(
select dense_rank() over(order by salary) as rank,
* from employees
)x where x.rank = n |
|
|
| |
Topic |
|
Article: Find Nth maximum value in SQL Server
