locked
how to select the max of 3 numbers in sql server 2005 RRS feed

  • Question

  • User-1244763459 posted

    Hi, how do I select the max of 3 or more numbers. For example, I need something like the following

    SELECT MAX(1,2,3) = 3? How can I do that?

    Sunday, December 7, 2008 10:42 AM

Answers

  • User1817007508 posted

    Just a set of variables, I was just wondering if I can just get the maximum number out of a set. If not, I can just use if else to get it since I only have 3 numbers.

    SELECT MAX(1,2,3,4,5) = 5

    Hi,

    You can use "table variable",  try the below example. I haven't tested it, but it will work for sure [:)]

    DECLARE @NumbersTable TABLE 
    (
    
      VarNumber INT
    
    )
    
    INSERT INTO @NumbersTable ([VarNumber]) VALUES(1)
    INSERT INTO @NumbersTable ([VarNumber]) VALUES(3)
    INSERT INTO @NumbersTable ([VarNumber]) VALUES(2)
    INSERT INTO @NumbersTable ([VarNumber]) VALUES(5)
    
    DECLARE @MaxNum INT
    
    SELECT @MaxNum  = MAX(NumTable.[VarNumber]) FROM @NumbersTable AS NumTable
    
    PRINT @MaxNum 
    
    

     Table variables are better than Temporary tables. Check this for more info http://www.odetocode.com/articles/365.aspx

    Check it and let me know whether it helped you or not [:)]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 7, 2008 1:04 PM
  • User1096912014 posted

    Here is what I just wrote to return minimum integer number for the comma-delimited string of integers

    IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ufn_Min')

    DROP FUNCTION ufn_Min

    GO

    CREATE FUNCTION ufn_Min

    (@STR VARCHAR(8000),

    @separator VARCHAR(16)=',')

    RETURNS int

    AS

    /* Splits passed string into items based on the specified separator string

    Parameters:

    @str - The string to split

    @separator - The separator string ( comma is default)

    Returns minimum integer of the list of integers - no error checking

    */

     

    BEGIN

    DECLARE @Item VARCHAR(128), @pos INT, @MinValue int, @CurValue int

    set @MinValue = 0

    WHILE DATALENGTH(@STR) > 0

    BEGIN

    SET @pos = CHARINDEX(@separator, @STR)

    IF @pos = 0 SET @pos = DATALENGTH(@STR)+1

     

    SET @Item = LEFT(@STR, @pos -1 )

    SET @CurValue = CAST(@Item as Int)

    IF @CurValue <=@MinValue SET @MinValue = @CurValue

     

    SET @STR = SUBSTRING(@STR, @pos + DATALENGTH(@separator), 8000)

    --INSERT INTO @TableArray (Item) VALUES(@Item)

    END

     

    RETURN @MinValue

     

    END

    go

    --declare @min int

    select dbo.ufn_Min('1,5,7,9,10,-32',',')

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 7, 2008 1:10 PM
  • User77042963 posted

    If the comma delimited numbers are in one column, you can use a UDF to split it first or split it on the fly. You can use the MAX function to get the maximum number from the reslt column. 

    Here is the sample code for how to do it with a CTE in SQL Server 2005 and 2008:

    declare @t table ( Col1 varchar(250)

    INSERT INTO @t(col1) VALUES('1,2,3,4,5')

    ;WITH NumsCTE
    AS
    (
    SELECT 1 as n
    UNION ALL
    SELECT n+1 FROM NumsCTE WHERE n<100

    )  

    SELECT MAX(CAST (element as Int)) as MaxNumber
    FROM (SELECT n-len(REPLACE(LEFT(Col1,n),',',''))+1 as pos,
    SUBSTRING(Col1,n,charindex(',',Col1+',',n)-n) as element
    FROM @t JOIN NumsCTE ON n<=len(Col1) AND SUBSTRING(','+Col1,n,1)=','
    ) t 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 7, 2008 1:56 PM

All replies

  • User-1956254743 posted

    Why cant you use a Temp table and insert these values in that temporary table for the use.

    Then query the MAX clause to the same and get the output.

    Sunday, December 7, 2008 11:30 AM
  • User1817007508 posted

    Hi, Could you detail your exact issue. do you want to select max of numbers from a table? or set of variables?

    Sunday, December 7, 2008 11:34 AM
  • User-1244763459 posted

     Just a set of variables, I was just wondering if I can just get the maximum number out of a set. If not, I can just use if else to get it since I only have 3 numbers.

    SELECT MAX(1,2,3,4,5) = 5

    Sunday, December 7, 2008 11:57 AM
  • User-1637698612 posted

     You might write a scalar function that receives an array and returns the max.  Google 'sql split' for examples.  You'd have to convert your variables into a comma delimited string, and use charindex for the split.  Save the values to a temp table or a table variable, converted back to int, and then take the max. The advantage is that you can use this for any number of variables.

    Sunday, December 7, 2008 12:54 PM
  • User1817007508 posted

    Just a set of variables, I was just wondering if I can just get the maximum number out of a set. If not, I can just use if else to get it since I only have 3 numbers.

    SELECT MAX(1,2,3,4,5) = 5

    Hi,

    You can use "table variable",  try the below example. I haven't tested it, but it will work for sure [:)]

    DECLARE @NumbersTable TABLE 
    (
    
      VarNumber INT
    
    )
    
    INSERT INTO @NumbersTable ([VarNumber]) VALUES(1)
    INSERT INTO @NumbersTable ([VarNumber]) VALUES(3)
    INSERT INTO @NumbersTable ([VarNumber]) VALUES(2)
    INSERT INTO @NumbersTable ([VarNumber]) VALUES(5)
    
    DECLARE @MaxNum INT
    
    SELECT @MaxNum  = MAX(NumTable.[VarNumber]) FROM @NumbersTable AS NumTable
    
    PRINT @MaxNum 
    
    

     Table variables are better than Temporary tables. Check this for more info http://www.odetocode.com/articles/365.aspx

    Check it and let me know whether it helped you or not [:)]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 7, 2008 1:04 PM
  • User1096912014 posted

    Here is what I just wrote to return minimum integer number for the comma-delimited string of integers

    IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ufn_Min')

    DROP FUNCTION ufn_Min

    GO

    CREATE FUNCTION ufn_Min

    (@STR VARCHAR(8000),

    @separator VARCHAR(16)=',')

    RETURNS int

    AS

    /* Splits passed string into items based on the specified separator string

    Parameters:

    @str - The string to split

    @separator - The separator string ( comma is default)

    Returns minimum integer of the list of integers - no error checking

    */

     

    BEGIN

    DECLARE @Item VARCHAR(128), @pos INT, @MinValue int, @CurValue int

    set @MinValue = 0

    WHILE DATALENGTH(@STR) > 0

    BEGIN

    SET @pos = CHARINDEX(@separator, @STR)

    IF @pos = 0 SET @pos = DATALENGTH(@STR)+1

     

    SET @Item = LEFT(@STR, @pos -1 )

    SET @CurValue = CAST(@Item as Int)

    IF @CurValue <=@MinValue SET @MinValue = @CurValue

     

    SET @STR = SUBSTRING(@STR, @pos + DATALENGTH(@separator), 8000)

    --INSERT INTO @TableArray (Item) VALUES(@Item)

    END

     

    RETURN @MinValue

     

    END

    go

    --declare @min int

    select dbo.ufn_Min('1,5,7,9,10,-32',',')

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 7, 2008 1:10 PM
  • User77042963 posted

    If the comma delimited numbers are in one column, you can use a UDF to split it first or split it on the fly. You can use the MAX function to get the maximum number from the reslt column. 

    Here is the sample code for how to do it with a CTE in SQL Server 2005 and 2008:

    declare @t table ( Col1 varchar(250)

    INSERT INTO @t(col1) VALUES('1,2,3,4,5')

    ;WITH NumsCTE
    AS
    (
    SELECT 1 as n
    UNION ALL
    SELECT n+1 FROM NumsCTE WHERE n<100

    )  

    SELECT MAX(CAST (element as Int)) as MaxNumber
    FROM (SELECT n-len(REPLACE(LEFT(Col1,n),',',''))+1 as pos,
    SUBSTRING(Col1,n,charindex(',',Col1+',',n)-n) as element
    FROM @t JOIN NumsCTE ON n<=len(Col1) AND SUBSTRING(','+Col1,n,1)=','
    ) t 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 7, 2008 1:56 PM
  • User1979029746 posted

    select top 3* from [Table] order by ColName DESC

    Friday, November 8, 2013 6:35 AM