Results 1 to 8 of 8

Thread: greatest value

  1. #1
    Join Date
    Feb 2002
    Posts
    1

    Unanswered: greatest value

    Hi all. I'm looking for assistance to get the greatest value from 3 or more different columns. I'm assuming that the best way is to put the columns into a temp table and use 'max' function to return the greatest value but don't know how to code it. Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE #minnest (
       a		INT
    ,  b		INT
    ,  c		INT
       )
    
    INSERT INTO #minnest (a, b, c)
       SELECT  1,  2,  3 UNION
       SELECT 11, 22, 33 UNION
       SELECT 21, 22, 23 UNION
       SELECT 31, 32, 33 UNION
       SELECT 41, 42, 43
    
    SELECT
       CASE
          WHEN Min(a) < Min(b) AND Min(a) < Min(c) THEN Min(a)
          WHEN Min(b) < Min(c) THEN Min(b)
          ELSE Min(c)
       END
       FROM #minnest
    
    DROP TABLE #minnest
    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've always had a preference for formula-based solutions over boolena logic:
    Code:
    create function dbo.Biggest(@Val1 decimal(38, 10), @Val2 decimal(38, 10))
    returns decimal(38, 10)
    as
    --Function Biggest
    --Returns the larger of two numbers
    --
    --blindman, 3/15/2006
    begin
    return (@Val1 + @Val2 + abs(@Val1 - @Val2))/2
    end
    Usage:
    Code:
    CREATE TABLE #minnest (
       a		INT
    ,  b		INT
    ,  c		INT
       )
    
    INSERT INTO #minnest (a, b, c)
       SELECT  1,  2,  3 UNION
       SELECT 11, 22, 33 UNION
       SELECT 21, 22, 23 UNION
       SELECT 31, 32, 33 UNION
       SELECT 41, 42, 43
    
    SELECT dbo.Biggest(a, dbo.Biggest(b, c)) FROM #minnest
    
    DROP TABLE #minnest
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47

    Thumbs up

    Alternate method
    Code:
    create function dbo.Biggest
    (
        @val1 decimal(38,10),
        @val2 decimal(38,10),
        @val3 decimal(38,10)
    )
    returns decimal(38,10)
    as
    begin
        return
        (
            select max(val)
            from
            (
                select @val1 as val union all
                select @val2 as val union all
                select @val3 as val
            ) as m
        )
    end
    go
    select dbo.Biggest(2, 4, 8)
    -----------------
    KH


  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    instead of putting your data into a temp table, put it into mysql where you can use the built-in GREATEST function

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quit pimpin' yo' lo-class engines on our street corner, Jack!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    Quit pimpin' yo' lo-class engines on our street corner, Jack!
    <voice type="bender">everyplace else it's "sql server can do this" and "sql server can do that" but do you guys like it when someone comes in here and shows you something sql server should be able to do but can't?noooooooooo-o-o-o-o</voice>

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    instead of putting your data into a temp table, put it into mysql where you can use the built-in GREATEST function
    There are actually a lot of languages that implement iterative functionality like the Greatest function. I think that Access, Perl, PHP, etc all either implement it directly (as delivered) or can easily create a function like Blindman did to add that kind of functionality.

    Min and Max aren't truly set based functions, but they are usually implemented in the database engine itself instead of in the DRL script interpreter. That makes Min and Max much more efficient than an interpreted function added after the row fetch has been completed such as Greatest.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •