Getting Max Value from list

  • In Excel, MAX(value_1, value_2, value_n) returns the highest value on the list. Is there a similar function in SQL Server?

  • Are the values in a table? There is a max() function. Look under aggregates in BOL.

  • The values are in multiple columns in a table.  I need a function that compares multiple values and determines the max value.  I know I'll probably end up writing my own function but I thought I'd check in and see if anyone has encountered the same issue.

     

    Pete

  • nothing for multiple columns.

  • You cannot Compare two columns unless you write some program in sql server. You can achive teh same by writing a UDF

    --------------------------------

  • If all columns the same datatype, ugly, poor performance but maybe ...

    SELECT MAX(maxval) as [maxval]

    FROM (

    SELECT MAX(col3) as [maxval] FROM

    UNION

    SELECT MAX(col4) as [maxval] FROM

    UNION

    SELECT MAX(col7) as [maxval] FROM

    UNION

    SELECT MAX(col8) as [maxval] FROM

    ) a

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Maybe you're looking for something like this ?

    Create Function MaxFromList (@String nvarchar(4000), @delimiter varchar(1) = ',')

    returns nvarchar(255)

    AS

    Begin

       declare @Datalength int

       declare @CurrentPosition int

       declare @NextPosition int

       declare @ThisTable table(Value nvarchar(255))

       declare @MaxWord nvarchar(255)

       set @CurrentPosition = 1

       set @Datalength = LEN(@String)

       while( @CurrentPosition <= @Datalength)

       begin

          set @NextPosition =

             case CharIndex(@delimiter,@String,@CurrentPosition)

                when 0 then @DataLength + 1

                else CharIndex(@delimiter,@String,@CurrentPosition)

             end /* case */

          insert into @ThisTable select cast(substring(@string,@Currentposition, (@NextPosition - @CurrentPosition)) as nvarchar(255))

          set @CurrentPosition = @NextPosition + 1

       end /* while */

    select @MaxWord = max(Value) from @ThisTable

    return @MaxWord

    end /* function body */

    Call it like this:

    select dbo.MaxFromList('1,22,33,456.55,3,4',default)

      Result = 456.55

    or

    select dbo.MaxFromList('anni-brian-Joan','-')

    result = Joan

     

  • Thanks, Rene, ALL.

    This solution should work nicely.

    P

Viewing 8 posts - 1 through 7 (of 7 total)

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