none
How to get max value from a row

    Question

  • Hi,
    MAX() function returns the max value from a specific column of the table, but i want to get the max value from a row.
    Assume the following table.

    col1    col2    col3    col4    col5    col6
    0         7         9        3         4
    5         6         10      8         1

    I want to update the col6 whith the max value in that row. For the above example col6 will have 9 in first row and 10 in second.

    I don't know the exact number of columns in the table. There can be any number of columns and i have to find the max from all columns.

    Can anybody help me in this regard.

    Thanks in advance.

    Regards,
    Sulaman Riaz
    Wednesday, April 2, 2008 7:11 AM

Answers

  • Here it is,

    Code Snippet

    Create Table #sample (

                    [col1] int ,

                    [col2] int ,

                    [col3] int ,

                    [col4] int ,

                    [col5] int 

    );

     

    Insert Into #sample Values('0','7','9','3','4');

    Insert Into #sample Values('5','6','10','8','1');

     

    --Solution

    select *, Row_Number() Over (Order By getdate()) Rid into #temp From #sample

     

    Declare @Cols as Varchar(max)

    Set @Cols=''

    select @Cols = @Cols + ',[' + name + ']' from tempdb..syscolumns where id=object_id('tempdb..#temp')

    and name <> 'Rid'

     

    Select @Cols = Right(@Cols,len(@Cols)-1)

    exec ('Select Rid,Max(val) maxval from #temp t

                                                    Unpivot(val For data in (' + @Cols + ')) as Upvt

                                    Group by Rid')

     

    Drop table #temp

    Drop table #sample

     

     

     

     

     

     

    Wednesday, April 2, 2008 8:37 AM

All replies

  • Hi, here is some possible solution:

     

    create table #tblMax (c1 int, c2 int , c3 int,cmx int)

    insert into #tblMax(c1,c2,c3)

    select 1,6,3

    insert into #tblMax(c1,c2,c3)

    select 6,7,8;

    with a as (

    SELECT rn, num

    FROM

    (SELECT cmx,row_number() over (order by cmx) as rn, c1, c2, c3

    FROM #tblMax) p

    UNPIVOT

    (num FOR cnm IN

    (c1, c2, c3)

    )AS unpvt

    ),

    b as (

    select

    rn,max(num) as maxn

    from a

    group by rn

    ),

    c as

    (

    select * ,

    row_number() over (order by cmx) as rnt

    from #tblMax

    )

    update c

    set cmx=maxn

    from c inner join b

    on b.rn=c.rnt

    select * from #tblMax

    drop table #tblMax

    Wednesday, April 2, 2008 7:46 AM

  • Thanks a lot. It works fine. But how can i find out the max value when i don't know the total number of colums in the table.
    Is there anything to do that without a loop.

    PS: I am using SQL Server 2005

    Regards,
    Sulaman Riaz
    Wednesday, April 2, 2008 7:58 AM
  • Hi,

    in my opinion the only solution in the case you don't know the precise structure of a table is dynamic sql

    Wednesday, April 2, 2008 8:03 AM
  • You have to use DYNAMIC SQL.

    You can get the column information like this:

    Code Snippet

    SELECT

    *

    FROM

    [AdventureWorks].[Information_Schema].[Columns]

    WHERE

    Table_Schema = 'PurChasing'

    AND

    Table_Name = 'Vendor'

    Here is an example for Dynamic SQL: http://www.sqlusa.com/bestpractices2005/dynamicpivot/



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Wednesday, April 2, 2008 8:12 AM
    Moderator
  • Here it is,

    Code Snippet

    Create Table #sample (

                    [col1] int ,

                    [col2] int ,

                    [col3] int ,

                    [col4] int ,

                    [col5] int 

    );

     

    Insert Into #sample Values('0','7','9','3','4');

    Insert Into #sample Values('5','6','10','8','1');

     

    --Solution

    select *, Row_Number() Over (Order By getdate()) Rid into #temp From #sample

     

    Declare @Cols as Varchar(max)

    Set @Cols=''

    select @Cols = @Cols + ',[' + name + ']' from tempdb..syscolumns where id=object_id('tempdb..#temp')

    and name <> 'Rid'

     

    Select @Cols = Right(@Cols,len(@Cols)-1)

    exec ('Select Rid,Max(val) maxval from #temp t

                                                    Unpivot(val For data in (' + @Cols + ')) as Upvt

                                    Group by Rid')

     

    Drop table #temp

    Drop table #sample

     

     

     

     

     

     

    Wednesday, April 2, 2008 8:37 AM

  • Thanks Everyone, The above code worked fine for me.


    Regards,
    Sulaman Riaz
    Wednesday, April 2, 2008 9:23 AM
  • here the dynamic procedure to useSadif you have an unique column, modify this SP):

     

    Code Snippet

    create proc UpdateWithMaxCol

    @TableName varchar(20), --e.g 'dbo.pvt'

    @WithinCols varchar(1000), --e.g 'Emp1, Emp2, Emp3, Emp4, Emp5'

    @UpdateColName varchar(20) --e.g 'test'

    as

    declare @sql nvarchar(max)

    set @sql='

    alter table ' + @TableName + '

    add temp_unique_id_col int IDENTITY(1,1) '

    exec sp_executesql @sql

    set @sql='update tmp set tmp.' + @UpdateColName + '=MaxValue

    from ' + @TableName + ' tmp join

    (

    SELECT temp_unique_id_col, max(value) MaxValue

    FROM

    (SELECT temp_unique_id_col, ' + @WithinCols + '

    FROM ' + @TableName + ') p

    UNPIVOT

    (value FOR Employee IN

    (' + @WithinCols + ')

    )AS unpvt

    group by temp_unique_id_col

    ) MaxTemp on MaxTemp.temp_unique_id_col=tmp.temp_unique_id_col '

    exec sp_executesql @sql

    set @sql='alter table ' + @TableName + '

    drop column temp_unique_id_col '

    exec sp_executesql @sql

     

     

    here is an e.g:

     

    Code Snippet

    CREATE TABLE test_table (Emp1 int, Emp2 int,

    Emp3 int, Emp4 int, Emp5 int,test int)

    GO

    INSERT INTO test_table VALUES (1,4,3,5,4,4,null)

    INSERT INTO test_table VALUES (2,4,1,6,5,5,null)

    INSERT INTO test_table VALUES (3,4,3,10,4,4,null)

    INSERT INTO test_table VALUES (4,4,2,5,5,4,null)

    INSERT INTO test_table VALUES (5,5,1,8,5,5,null)

    GO

    select * from test_table

    exec dbo.UpdateWithMaxCol

    @TableName = 'dbo.test_table',

    @WithinCols = 'Emp1, Emp2, Emp3, Emp4, Emp5',

    @UpdateColName ='test'

    select * from test_table

     

     

    Wednesday, April 2, 2008 9:47 AM
  • This worked perfect for me.  I had a file with 'dirty' data, meaning duplicate values that didn't belong there.

    SELECT TOP 1 Table.Column

    FROM Table

    ORDER BY Table.Column DESC

    This will yield only the highest value in the set, and because you instructed it to SELECT TOP 1, it will only return a single value.

    Hope this works for you.

    Wednesday, September 17, 2014 11:37 AM