Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Find Min/Max Values in a Set Expand / Collapse
Author
Message
Posted Sunday, November 16, 2003 12:00 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:38 PM
Points: 912, Visits: 209
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dasanka/findminmaxvaluesinaset.asp






My Blog: http://dineshasanka.spaces.live.com/
Post #18264
Posted Thursday, November 20, 2003 9:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:22 PM
Points: 166, Visits: 179
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



Post #87269
Posted Friday, November 21, 2003 2:41 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 6, 2013 2:16 AM
Points: 693, Visits: 124
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



Post #87270
Posted Friday, November 21, 2003 3:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:38 PM
Points: 912, Visits: 209
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: http://dineshasanka.spaces.live.com/
Post #87271
Posted Friday, November 21, 2003 5:34 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, November 5, 2005 6:09 AM
Points: 466, Visits: 1
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




Post #87272
Posted Friday, November 21, 2003 7:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 26, 2007 11:41 AM
Points: 81, Visits: 1
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





Post #87273
Posted Friday, November 21, 2003 8:28 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 6, 2013 2:16 AM
Points: 693, Visits: 124
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




Post #87274
Posted Friday, November 21, 2003 9:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 27, 2009 12:56 AM
Points: 348, Visits: 7
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




Post #87275
Posted Friday, November 21, 2003 10:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, November 5, 2005 6:09 AM
Points: 466, Visits: 1
declare @n int,
@s varchar(1000)
set @n=10
set @s ='select min(your_field) from your_table where your_field in
(select top ' + convert(varchar(10,@n) + ' your_field from your_table order by your_field desc)'

execute (@s)

Another solution with runtime sql.

Adrian




Post #87276
Posted Friday, November 21, 2003 11:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 1, 2010 3:31 AM
Points: 149, Visits: 19
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)




Post #87277
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse