SQL MAX of multiple columns?

  • How would you return 1 value per row of the max of several columns:


    [Number, Date1, Date2, Date3, Cost]

    I need to return something like this:

    [Number, Most_Recent_Date, Cost]

    Can anyone help me out with this.

    Thanks in advance

    • This topic was modified 2 months, 3 weeks ago by  Piyushbhatt.
  • This was removed by the editor as SPAM

  • Your exact requirement is not clear to me. (Tip: include CREATE TABLE + INSERT with sample data and the expected result). But here is the general pattern for this type of problem. I hope you can apply it to your table.

    ; WITH numbering AS (
       SELECT col1, col2, ..., rownum = row_number(PARITION BY col1, col2 ORDER BY col3 DESC, col4 DESC
       FROM tbl
    SELECT ...
    FROM numbering
    WHERE row_number = 1


    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Prior post referencing S.O. was flagged as spam?!?, so here's an abridged MSSQL version w/o links:

    SELECT [Number], [Cost],
    (SELECT Max(v) FROM (VALUES (date1), (date2), (date3)) AS value(v)) as [MaxDate]
    FROM [YourTableName]

    GREATEST() function isn't available in MSSQL yet as it is on other popular RDBMS.

  • A more primitive alternative to the above replies. 🙂

    declare @table table (
    Number int,
    Date1 date,
    Date2 date,
    Date3 date,
    Cost dec(13,2)

    insert into @table (Number, Date1, Date2, Date3, Cost)

    from @table t
    cross apply (select case when t.date1 > t.date2 then t.date1 else t.date2 end as Most_Recent_Date) mr1
    cross apply (select case when mr1.Most_Recent_Date > t.Date3 then mr1.Most_Recent_Date else t.Date3 end as Most_Recent_Date) mr2
  • These two statements are equivalent afaik.  Beginning with [Edit] 2022 (or compatibility level 150 in Azure SQL) they added the GREATEST function.  Prior ways were UNPIVOT using the built-in verb or using VALUES and stacking the columns up yourself.  The second way seems more intuitive and has fewer limitations imo

    /* sql server 2022+ */ 
    select greatest(c1, c2, c3)
    from (values (3, 5, 6)) v(c1, c2, c3);

    /* prior */
    select max(unpvt.dt)
    from (values (3, 5, 6)) v(c1, c2, c3)
    cross apply (values (c1),
    (c3)) unpvt(dt);

    • This reply was modified 2 months, 3 weeks ago by  Steve Collins.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • least/greatest are not available in SQL 2019, but they will appear in SQL 2022. It is correct that they are avialable i Azure SQL Databsae and Azure Managed Instance.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

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

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