July 12, 2022 at 3:29 pm
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
July 12, 2022 at 5:21 pm
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]
July 12, 2022 at 5:42 pm
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.
July 12, 2022 at 5:56 pm
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
July 12, 2022 at 5:57 pm
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);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 12, 2022 at 7:48 pm
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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy