Find Min/Max Values in a Set

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Dinesh Asanka » Find Min/Max Values in a Set

12 posts, Page 1 of 2

1 2 »»

Find Min/Max Values in a Set

Rate Topic

Display Mode

Topic Options

Author

Message

Dinesh Asanka

Posted Sunday, November 16, 2003 12:00 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:30 PM
Points: 912, Visits: 198

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

Nigel

Posted Thursday, November 20, 2003 9:06 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 PM
Points: 159, Visits: 148

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

Razvan Socol

Posted Friday, November 21, 2003 2:41 AM

Say Hey Kid

Group: General Forum Members
Last Login: Thursday, December 13, 2012 1:38 AM
Points: 693, Visits: 123

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

Dinesh Asanka

Posted Friday, November 21, 2003 3:01 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:30 PM
Points: 912, Visits: 198

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

baxi

Posted Friday, November 21, 2003 5:34 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, November 05, 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

MarcO-85539

Posted Friday, November 21, 2003 7:44 AM

SSC 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

Razvan Socol

Posted Friday, November 21, 2003 8:28 AM

Say Hey Kid

Group: General Forum Members
Last Login: Thursday, December 13, 2012 1:38 AM
Points: 693, Visits: 123

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

SeekQuel

Posted Friday, November 21, 2003 9:43 AM

Old 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

baxi

Posted Friday, November 21, 2003 10:30 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, November 05, 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

gamcall

Posted Friday, November 21, 2003 11:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 01, 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 »

12 posts, Page 1 of 2

1 2 »»

Permissions