Professional Web Applications Themes

MAX of two values - Microsoft SQL / MS SQL Server

I'm converting an old power builder app over to stored procs and would like to convert the MAX fuunction to something I can use in MS SQL 2000. I've tried stupid things like: select (myvar > 0)*myvar select max(1,2,3,4) etc... Any simple ideas that don't involve 'IF'...

  1. #1

    Default MAX of two values

    I'm converting an old power builder app over to stored
    procs and would like to convert the MAX fuunction to
    something I can use in MS SQL 2000.

    I've tried stupid things like:

    select (myvar > 0)*myvar
    select max(1,2,3,4)
    etc...

    Any simple ideas that don't involve 'IF'
    Leythos Guest

  2. #2

    Default Re: MAX of two values

    You can use a UDF:

    create function dbo.MyMax
    (
    a int
    , b int
    )
    returns int
    as
    begin
    return (select case when a > b then a else b end)
    end
    go

    select dbo.MyMax (1, 2)
    select dbo.MyMax (1, dbo.MyMax (2, 3))

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Leythos" <com> wrote in message news:031c01c34fba$6f38f160$gbl...
    I'm converting an old power builder app over to stored
    procs and would like to convert the MAX fuunction to
    something I can use in MS SQL 2000.

    I've tried stupid things like:

    select (myvar > 0)*myvar
    select max(1,2,3,4)
    etc...

    Any simple ideas that don't involve 'IF'

    Tom Guest

  3. #3

    Default Re: MAX of two values

    Here's an example:

    CREATE TABLE SomeValues (X INTEGER PRIMARY KEY, Y INTEGER NULL, Z INTEGER
    NULL)

    INSERT INTO SomeValues VALUES (1,2,3)
    INSERT INTO SomeValues VALUES (2,1,3)
    INSERT INTO SomeValues VALUES (3,2,NULL)

    SELECT *,
    (SELECT MIN(v)
    FROM (SELECT X AS v UNION ALL SELECT Y UNION ALL SELECT Z) m
    HAVING COUNT(v)=COUNT(*) -- Result only if all values are non-NULL
    )
    AS minimum,
    (SELECT MAX(v)
    FROM (SELECT X AS v UNION ALL SELECT Y UNION ALL SELECT Z) m
    HAVING COUNT(v)=COUNT(*) -- Result only if all values are non-NULL
    )
    AS maximum
    FROM SomeValues

    If you want to ignore NULLs then drop the HAVING clause.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: MAX of two values

    You can use the CASE statement in T-SQL to create a user-defined function
    like:

    CREATE FUNCTION (AParm INT, BParm INT)
    RETURNS INT
    AS
    DECLARE ReturnVal INT
    CASE
    WHEN AParm >= BParm THEN
    SET ReturnVal = AParm
    ELSE
    SET ReturnVal = BParm
    END
    RETURN (ReturnVal)



    Russell Fields

    "Leythos" <com> wrote in message
    news:031c01c34fba$6f38f160$gbl... 


    Russell Guest

  5. #5

    Default Re: MAX of two values

    There is no built-in method for doing this.

    You could do this:

    CREATE TABLE ints(i INT)
    INSERT ints VALUES(1)
    INSERT ints VALUES(2)
    INSERT ints VALUES(3)
    INSERT ints VALUES(4)
    SELECT MAX(i) FROM ints

    Or you could do this:

    SELECT Max(nbr) FROM (SELECT nbr = 1 UNION SELECT 2 UNION SELECT 3 UNION
    SELECT 4) n

    Or you could use a comma-separated list to generate a table, e.g. see
    http://www.aspfaq.com/2248





    "Leythos" <com> wrote in message
    news:031c01c34fba$6f38f160$gbl... 


    Aaron Guest

  6. #6

    Default MAX of two values

    Thanks everyone.

    I like the function and the methods using case. While I
    was already aware of those I was hoping that someone had a
    little trick in their back pocket.

    Thanks again,
    Leythos

     
    Leythos Guest

  7. #7

    Default Re: MAX of two values

    Are you always going to have 4 values? If not, it will be tricky to nest
    function calls and/or CASE ...

    This is why I suggested the table method... if you have a comma-separated
    list, it would be trivial to jam those into a table and use SELECT MAX() the
    way it was meant to be used...

    A


    "Leythos" <com> wrote in message
    news:016b01c34fbe$fdca25d0$gbl... 


    Aaron Guest

Similar Threads

  1. read row values from datagrid and populate in new datagrid values from connected
    By antonia gogaki via DotNetMonster.com in forum ASP.NET Data Grid Control
    Replies: 5
    Last Post: April 21st, 01:38 PM
  2. ASP getting values
    By LC in forum ASP Components
    Replies: 0
    Last Post: July 28th, 05:19 PM
  3. how to get URL values into swf - pls help me
    By ssipl in forum Macromedia Flash Actionscript
    Replies: 2
    Last Post: February 14th, 02:36 PM
  4. Why don?t the location values in the Sprite Overlay agree with the values
    By adiabatic webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 3
    Last Post: January 6th, 04:30 AM
  5. Adding custom values and database values to DropDwonList
    By William F. Robertson, Jr. in forum ASP.NET General
    Replies: 0
    Last Post: July 1st, 06:32 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139