SQL MAX of multiple columns?

Question

SQL MAX of multiple columns?

Piyushbhatt

Old Hand

Points: 374
More actions
July 12, 2022 at 3:29 pm

#4062197
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 years, 5 months ago by Piyushbhatt.
July 12, 2022 at 4:39 pm

This was removed by the editor as SPAM

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

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]

ratbak Ten Centuries Points: 1381 More actions · Answer 2

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.

kaj SSCommitted Points: 1607 More actions · Answer 3

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

Steve Collins SSCrazy Points: 2173 More actions · Answer 4

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 years, 5 months ago by Steve Collins.

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

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]