SQL MAX of multiple columns?

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

    TableName

    [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)
    values
    (1,'20220127','20220305','20220103',245.25),
    (2,'20220517','20220413','20220617',258.69),
    (3,'20220712','20220519','20220111',69.58)

    select
    t.Number,
    mr2.Most_Recent_Date,
    t.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
    Number;Most_Recent_Date;Cost
    1;2022-03-05;245.25
    2;2022-06-17;258.69
    3;2022-07-12;69.58
  • 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),
    (c2),
    (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