November 16, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dasanka/findminmaxvaluesinaset.asp
My Blog:
November 20, 2003 at 9:06 pm
I got a result very similar to yours:
select count(*) as rank, max(s1.number) as number
from(
select number from NMaxMin group by number
) as s1
inner join
(
select number from NMaxMin group by number
) as s2 on s1.number <= s2.number
group by s1.number
having count(*) = 5
(Just change the join condition to seach for top or bottom values.)
Edited by - pegasus on 11/20/2003 9:07:45 PM
November 21, 2003 at 2:41 am
Here are two shorter queries that do the same thing as your queries:
select distinct (select count(distinct number) from nMaxMin s2 where
s2.number<=s1.number) as rank, number from nMaxMin s1 order by 1
select number from nMaxMin s1 WHERE (select count(distinct number)
from nMaxMin s2 where s2.number>=s1.number)=5
Without indexes, these queries run slightly slower than yours.
But if we add an index, for example: CREATE INDEX ix ON nMaxMin(Number)
this queries will be faster.
Razvan
Edited by - rsocol on 11/21/2003 02:48:10 AM
November 21, 2003 at 3:01 am
I accept UR opininon
But the question is whether to have index on this type of field.
Say,Are we going to add an index to Qty field
My Blog:
November 21, 2003 at 5:34 am
An other solution is:
declare @n int
select min(your_field) from your_table where your_field in
(select top @n your_field from your_table order by your_field desc)
For me is more elegant.
Adrian
November 21, 2003 at 7:44 am
If you are trying to get the 5th greatest number in the list how about this?
declare @Answer int
select distinct top 5 @Answer = Number from nMaxMin order by Number desc
select @Answer
The 5th smallest would of course be
declare @Answer int
select distinct top 5 @Answer = Number from nMaxMin order by Number asc
select @Answer
November 21, 2003 at 8:28 am
Adrian,
Your solution would be more elegant indeed, but it doesn't work.
Unfortunately, the TOP keyword accepts only a constant, not a variable.
Dinesh,
Indeed, I would not add an index to a Qty column.
Razvan
November 21, 2003 at 9:43 am
ORACLE gives unique rowID's to SQL result sets, the author is right to point out that this is really cool.
For this question, however, SQL server allows very elegant solutions.
To get the 5th maximum:
SELECT Min(Ordered_Value_List.Quantity)
FROM
(
SELECT TOP 5 [Quantity]
FROM dbo.[Sales Line]
ORDER BY [Quantity] DESC
) Ordered_Value_List
And for 5th minimum:
SELECT MAX(Ordered_Value_List.Quantity)
FROM
(
SELECT TOP 5 [Quantity]
FROM dbo.[Sales Line]
ORDER BY [Quantity] ASC
) Ordered_Value_List
November 21, 2003 at 11:25 am
Hasn't anyone here (or who read the Oracle magazine) heard of correlated sub-queries?
select aField from aTable t1
where 2 = (select count(*) from aTable t2
where t2.aField > t1.aField)
November 24, 2003 at 9:29 pm
Thankx for the comments. Which help me a lot.
This my first article in this forum. from a single article I learnt a lot
Thankx every body.
My Blog:
November 23, 2004 at 6:08 pm
I know cursors aren't popular, and most of the times have worse performance, but sometimes they might do the trick elegantly and in this case probably efficiantly (although I haven't checked this)... You can declare a scrollable cursor for the simple "select distinct col from table order by col desc/asc" and then use FETCH RELATIVE -N to fetch the nth ranked...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply