Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find Min/Max Values in a Set


Find Min/Max Values in a Set

Author
Message
Dinesh Asanka
Dinesh Asanka
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 223
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/
Nigel
Nigel
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 234
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



Razvan Socol
Razvan Socol
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 129
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



Dinesh Asanka
Dinesh Asanka
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 223
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/
baxi
baxi
SSC-Addicted
SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)

Group: General Forum Members
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



MarcO-85539
MarcO-85539
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
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



Razvan Socol
Razvan Socol
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 129
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



SeekQuel
SeekQuel
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
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



baxi
baxi
SSC-Addicted
SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)

Group: General Forum Members
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



gamcall
gamcall
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
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)



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search