Find Min/Max Values in a Set

  • Dinesh Asanka

    SSChampion

    Points: 11058

    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

    SSC-Addicted

    Points: 485

    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

    SSCarpal Tunnel

    Points: 4758

    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

    SSChampion

    Points: 11058

    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

    Say Hey Kid

    Points: 690

    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

    SSC Enthusiast

    Points: 153

    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

    SSCarpal Tunnel

    Points: 4758

    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

    SSCommitted

    Points: 1818

    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

    Say Hey Kid

    Points: 690

    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

    Old Hand

    Points: 315

    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)

  • Dinesh Asanka

    SSChampion

    Points: 11058

    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: http://dineshasanka.spaces.live.com/

  • Alon Biran

    SSC Journeyman

    Points: 93

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply