ChatGPT解决这个技术问题 Extra ChatGPT

SQL MAX of multiple columns?

How do 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]

Query?


C
Chris Weber

Here is another nice solution for the Max functionality using T-SQL and SQL Server

SELECT [Other Fields],
  (SELECT Max(v) 
   FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]

Values is the Table Value Constructor.

"Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified either as the VALUES clause of an INSERT ... VALUES statement, or as a derived table in either the USING clause of the MERGE statement or the FROM clause."


SQL version must be >= 2008.
This does work very well with 2008 and handles NULLs. Very nice solution.
@Cheburek: From value(v), "value" is the alias for the virtual table and "v" is the name of the virtual column of the date values.
This is brilliant. Where can I find the documentation for this Value() virtual table?
I initially didn't understand VALUE(v) either. If you want to understand VALUE try this query which creates a virtual 1 column table: SELECT * FROM (VALUES (1), (5), (1)) as listOfValues(columnName) And this query which creates a virtual 2 column table: SELECT * FROM (VALUES (1,2), (5,3), (1,4)) as tableOfValues(columnName1, ColumnName2) Now you can understand why that sample query has AS value(v) in it. My final query looked like this: SELECT Max(currentValues) as Max FROM (VALUES (12), (25), (35)) AS allCurrents(currentValues) It will pick the max value which in this case is 35.
S
Scinana

If you're using MySQL or PostgreSQL or Oracle, you can use

SELECT GREATEST(col1, col2 ...) FROM table

True, but still a very helpful answer as people find this question in reference to MySQL.
Also available in PostgreSQL from 8.1.
Doesn't handle NULL's well, but if you coalesce(col1, 0) around your column values you'll be cooking with gas see this answer stackoverflow.com/questions/9831851/…
And what about this solution: stackoverflow.com/a/2166693/4824854
I just used this in TOAD for Oracle SQL and it worked.
C
Chris Weber

This is an old answer and broken in many way.

See https://stackoverflow.com/a/6871572/194653 which has way more upvotes and works with sql server 2008+ and handles nulls, etc.

Original but problematic answer:

Well, you can use the CASE statement:

SELECT
    CASE
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
        WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
        WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
        ELSE                                        Date1
    END AS MostRecentDate

Wouldn't it suffice to use WHEN Date1 > Date2 AND Date1 > Date3 THEN Date1; WHEN Date2 > Date3 THEN Date3; ELSE Date3?
The obvious answer, but it doesn't work with NULL values, and attempting to fix that gets very messy.
Necro'ing this older post, but you could wrap each date into a COALESCE to handle NULL's. One of those WHEN statements would then look like: WHEN Date1 >= COALESCE(Date2,'') AND Date1 >= COALESCE(Date3,'') THEN Date3 (do the same for the other when's)
BTW, it returns Date1 when Date2 is null even if Date3>Date1.
Really this answer should be removed since its so bad. Not sure how it got 178 upvotes really, it doesn't work at all if you have NULL values in any of your dates, which is very common.
M
Michael

There are 3 more methods where UNPIVOT (1) is the fastest by far, followed by Simulated Unpivot (3) which is much slower than (1) but still faster than (2)

CREATE TABLE dates
    (
      number INT PRIMARY KEY ,
      date1 DATETIME ,
      date2 DATETIME ,
      date3 DATETIME ,
      cost INT
    )

INSERT  INTO dates
VALUES  ( 1, '1/1/2008', '2/4/2008', '3/1/2008', 10 )
INSERT  INTO dates
VALUES  ( 2, '1/2/2008', '2/3/2008', '3/3/2008', 20 )
INSERT  INTO dates
VALUES  ( 3, '1/3/2008', '2/2/2008', '3/2/2008', 30 )
INSERT  INTO dates
VALUES  ( 4, '1/4/2008', '2/1/2008', '3/4/2008', 40 )
GO

Solution 1 (UNPIVOT)

SELECT  number ,
        MAX(dDate) maxDate ,
        cost
FROM    dates UNPIVOT ( dDate FOR nDate IN ( Date1, Date2,
                                            Date3 ) ) as u
GROUP BY number ,
        cost 
GO

Solution 2 (Sub query per row)

SELECT  number ,
        ( SELECT    MAX(dDate) maxDate
          FROM      ( SELECT    d.date1 AS dDate
                      UNION
                      SELECT    d.date2
                      UNION
                      SELECT    d.date3
                    ) a
        ) MaxDate ,
        Cost
FROM    dates d
GO

Solution 3 (Simulated UNPIVOT)

;WITH    maxD
          AS ( SELECT   number ,
                        MAX(CASE rn
                              WHEN 1 THEN Date1
                              WHEN 2 THEN date2
                              ELSE date3
                            END) AS maxDate
               FROM     dates a
                        CROSS JOIN ( SELECT 1 AS rn
                                     UNION
                                     SELECT 2
                                     UNION
                                     SELECT 3
                                   ) b
               GROUP BY Number
             )
    SELECT  dates.number ,
            maxD.maxDate ,
            dates.cost
    FROM    dates
            INNER JOIN MaxD ON dates.number = maxD.number
GO

DROP TABLE dates
GO

Nice. I was unaware of the PIVOT and UNPIVOT operators.
Any idea which versions of SQL Server support pivot/unpivot?
@CraigYoung SQL Server 2005 with COMPATIBILITY_LEVEL set to 90.
Nice! Unconventional use of PIVOT / UNPIVOT. Any reference or proof for "fastest by far"??
M
MartinC

Scalar Function cause all sorts of performance issues, so its better to wrap the logic into an Inline Table Valued Function if possible. This is the function I used to replace some User Defined Functions which selected the Min/Max dates from a list of upto ten dates. When tested on my dataset of 1 Million rows the Scalar Function took over 15 minutes before I killed the query the Inline TVF took 1 minute which is the same amount of time as selecting the resultset into a temporary table. To use this call the function from either a subquery in the the SELECT or a CROSS APPLY.

CREATE FUNCTION dbo.Get_Min_Max_Date
(
    @Date1  datetime,
    @Date2  datetime,
    @Date3  datetime,
    @Date4  datetime,
    @Date5  datetime,
    @Date6  datetime,
    @Date7  datetime,
    @Date8  datetime,
    @Date9  datetime,
    @Date10 datetime
)
RETURNS TABLE
AS
RETURN
(
    SELECT      Max(DateValue)  Max_Date,
                Min(DateValue)  Min_Date
    FROM        (
                    VALUES  (@Date1),
                            (@Date2),
                            (@Date3),
                            (@Date4),
                            (@Date5),
                            (@Date6),
                            (@Date7),
                            (@Date8),
                            (@Date9),
                            (@Date10)
                )   AS Dates(DateValue)
)

After 10 years I can't believe you only got 9 points (plus mine) for this answer. People don't seem to realise the efficiency, clarity, readability, etc. Well done!
Does it handle nulls well?
C
Community

Either of the two samples below will work:

SELECT  MAX(date_columns) AS max_date
FROM    ( (SELECT   date1 AS date_columns
           FROM     data_table         )
          UNION
          ( SELECT  date2 AS date_columns
            FROM    data_table
          )
          UNION
          ( SELECT  date3 AS date_columns
            FROM    data_table
          )
        ) AS date_query

The second is an add-on to lassevk's answer.

SELECT  MAX(MostRecentDate)
FROM    ( SELECT    CASE WHEN date1 >= date2
                              AND date1 >= date3 THEN date1
                         WHEN date2 >= date1
                              AND date2 >= date3 THEN date2
                         WHEN date3 >= date1
                              AND date3 >= date2 THEN date3
                         ELSE date1
                    END AS MostRecentDate
          FROM      data_table
        ) AS date_query 

First answer is good, but can be significantly simplified. Second answer doesn't work with NULL values. Attempting to fix that problem gets very messy.
You should use UNION ALL and not UNION to avoid an unnecessary implied DISTINCT operation.
j
jjaskulowski

For T-SQL (MSSQL 2008+)

SELECT
  (SELECT
     MAX(MyMaxName) 
   FROM ( VALUES 
            (MAX(Field1)), 
            (MAX(Field2)) 
        ) MyAlias(MyMaxName)
  ) 
FROM MyTable1

This exact solution was suggested back in 2011
M
Martin Smith
DECLARE @TableName TABLE (Number INT, Date1 DATETIME, Date2 DATETIME, Date3 DATETIME, Cost MONEY)

INSERT INTO @TableName 
SELECT 1, '20000101', '20010101','20020101',100 UNION ALL
SELECT 2, '20000101', '19900101','19980101',99 

SELECT Number,
       Cost  ,
       (SELECT MAX([Date])
       FROM    (SELECT Date1 AS [Date]
               UNION ALL
               SELECT Date2
               UNION ALL
               SELECT Date3
               )
               D
       )
       [Most Recent Date]
FROM   @TableName

Worked in any SQL version for me, nice solution
N
Nat
SELECT 
    CASE 
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1 
        WHEN Date2 >= Date3 THEN Date2 
        ELSE Date3
    END AS MostRecentDate 

This is slightly easier to write out and skips evaluation steps as the case statement is evaluated in order.


Careful. If Date2 is NULL, the answer will be Date3; even if Date1 is bigger.
C
Community

Unfortunately Lasse's answer, though seemingly obvious, has a crucial flaw. It cannot handle NULL values. Any single NULL value results in Date1 being returned. Unfortunately any attempt to fix that problem tends to get extremely messy and doesn't scale to 4 or more values very nicely.

databyss's first answer looked (and is) good. However, it wasn't clear whether the answer would easily extrapolate to 3 values from a multi-table join instead of the simpler 3 values from a single table. I wanted to avoid turning such a query into a sub-query just to get the max of 3 columns, also I was pretty sure databyss's excellent idea could be cleaned up a bit.

So without further ado, here's my solution (derived from databyss's idea). It uses cross-joins selecting constants to simulate the effect of a multi-table join. The important thing to note is that all the necessary aliases carry through correctly (which is not always the case) and this keeps the pattern quite simple and fairly scalable through additional columns.

DECLARE @v1 INT ,
        @v2 INT ,
        @v3 INT
--SET @v1 = 1 --Comment out SET statements to experiment with 
              --various combinations of NULL values
SET @v2 = 2
SET @v3 = 3

SELECT  ( SELECT    MAX(Vals)
          FROM      ( SELECT    v1 AS Vals
                      UNION
                      SELECT    v2
                      UNION
                      SELECT    v3
                    ) tmp
          WHERE     Vals IS NOT NULL -- This eliminates NULL warning

        ) AS MaxVal
FROM    ( SELECT    @v1 AS v1
        ) t1
        CROSS JOIN ( SELECT @v2 AS v2
                   ) t2
        CROSS JOIN ( SELECT @v3 AS v3
                   ) t3

C
Community

Problem: choose the minimum rate value given to an entity Requirements: Agency rates can be null

[MinRateValue] = 
CASE 
   WHEN ISNULL(FitchRating.RatingValue, 100) < = ISNULL(MoodyRating.RatingValue, 99) 
   AND  ISNULL(FitchRating.RatingValue, 100) < = ISNULL(StandardPoorsRating.RatingValue, 99) 
   THEN FitchgAgency.RatingAgencyName

   WHEN ISNULL(MoodyRating.RatingValue, 100) < = ISNULL(StandardPoorsRating.RatingValue , 99)
   THEN MoodyAgency.RatingAgencyName

   ELSE ISNULL(StandardPoorsRating.RatingValue, 'N/A') 
END 

Inspired by this answer from Nat


L
Lance Fisher

If you are using SQL Server 2005, you can use the UNPIVOT feature. Here is a complete example:

create table dates 
(
  number int,
  date1 datetime,
  date2 datetime,
  date3 datetime 
)

insert into dates values (1, '1/1/2008', '2/4/2008', '3/1/2008')
insert into dates values (1, '1/2/2008', '2/3/2008', '3/3/2008')
insert into dates values (1, '1/3/2008', '2/2/2008', '3/2/2008')
insert into dates values (1, '1/4/2008', '2/1/2008', '3/4/2008')

select max(dateMaxes)
from (
  select 
    (select max(date1) from dates) date1max, 
    (select max(date2) from dates) date2max,
    (select max(date3) from dates) date3max
) myTable
unpivot (dateMaxes For fieldName In (date1max, date2max, date3max)) as tblPivot

drop table dates

I think I like the UNION example better.
"How do you return ONE VALUE PER ROW of the max of several columns"
E
EarlOfEnnui

Using CROSS APPLY (for 2005+) ....

SELECT MostRecentDate 
FROM SourceTable
    CROSS APPLY (SELECT MAX(d) MostRecentDate FROM (VALUES (Date1), (Date2), (Date3)) AS a(d)) md

Using VALUES prevents this from working on 2005. You'd have to replace VALUES with an equivalent series of SELECT value UNION ALL SELECT value UNION ALL ….
a
abdulbasit

From SQL Server 2012 we can use IIF.

 DECLARE @Date1 DATE='2014-07-03';
 DECLARE @Date2 DATE='2014-07-04';
 DECLARE @Date3 DATE='2014-07-05';

 SELECT IIF(@Date1>@Date2,
        IIF(@Date1>@Date3,@Date1,@Date3),
        IIF(@Date2>@Date3,@Date2,@Date3)) AS MostRecentDate

Pretty nice, but doesn't handle nulls. For example: DECLARE @Date1 DATE='2014-08-01'; DECLARE @Date2 DATE=null; DECLARE @Date3 DATE='2014-07-05'; /*this gets returned*/
We could handle nulls like this: select IIF(@Date1 > @Date2 or @Date2 is null, IIF(@Date1 > @Date3 or @Date3 is null, @Date1, @Date3), IIF(@Date2 > @Date3 or @Date3 is null, @Date2, @Date3)) as MostRecentDate
T
TechDo

Please try using UNPIVOT:

SELECT MAX(MaxDt) MaxDt
   FROM tbl 
UNPIVOT
   (MaxDt FOR E IN 
      (Date1, Date2, Date3)
)AS unpvt;

R
Robert Lujo

I prefer solutions based on case-when, my assumption is that it should have the least impact on possible performance drop compared to other possible solutions like those with cross-apply, values(), custom functions etc.

Here is the case-when version that handles null values with most of possible test cases:

SELECT
    CASE 
        WHEN Date1 > coalesce(Date2,'0001-01-01') AND Date1 > coalesce(Date3,'0001-01-01') THEN Date1 
        WHEN Date2 > coalesce(Date3,'0001-01-01') THEN Date2 
        ELSE Date3
    END AS MostRecentDate
    , *
from 
(values
     (  1, cast('2001-01-01' as Date), cast('2002-01-01' as Date), cast('2003-01-01' as Date))
    ,(  2, cast('2001-01-01' as Date), cast('2003-01-01' as Date), cast('2002-01-01' as Date))
    ,(  3, cast('2002-01-01' as Date), cast('2001-01-01' as Date), cast('2003-01-01' as Date))
    ,(  4, cast('2002-01-01' as Date), cast('2003-01-01' as Date), cast('2001-01-01' as Date))
    ,(  5, cast('2003-01-01' as Date), cast('2001-01-01' as Date), cast('2002-01-01' as Date))
    ,(  6, cast('2003-01-01' as Date), cast('2002-01-01' as Date), cast('2001-01-01' as Date))
    ,( 11, cast(NULL         as Date), cast('2002-01-01' as Date), cast('2003-01-01' as Date))
    ,( 12, cast(NULL         as Date), cast('2003-01-01' as Date), cast('2002-01-01' as Date))
    ,( 13, cast('2003-01-01' as Date), cast(NULL         as Date), cast('2002-01-01' as Date))
    ,( 14, cast('2002-01-01' as Date), cast(NULL         as Date), cast('2003-01-01' as Date))
    ,( 15, cast('2003-01-01' as Date), cast('2002-01-01' as Date), cast(NULL         as Date))
    ,( 16, cast('2002-01-01' as Date), cast('2003-01-01' as Date), cast(NULL         as Date))
    ,( 21, cast('2003-01-01' as Date), cast(NULL         as Date), cast(NULL         as Date))
    ,( 22, cast(NULL         as Date), cast('2003-01-01' as Date), cast(NULL         as Date))
    ,( 23, cast(NULL         as Date), cast(NULL         as Date), cast('2003-01-01' as Date))
    ,( 31, cast(NULL         as Date), cast(NULL         as Date), cast(NULL         as Date))

) as demoValues(id, Date1,Date2,Date3)
order by id
;

and the result is:

MostRecent    id   Date1      Date2      Date3
2003-01-01    1    2001-01-01 2002-01-01 2003-01-01
2003-01-01    2    2001-01-01 2003-01-01 2002-01-01
2003-01-01    3    2002-01-01 2001-01-01 2002-01-01
2003-01-01    4    2002-01-01 2003-01-01 2001-01-01
2003-01-01    5    2003-01-01 2001-01-01 2002-01-01
2003-01-01    6    2003-01-01 2002-01-01 2001-01-01
2003-01-01    11   NULL       2002-01-01 2003-01-01
2003-01-01    12   NULL       2003-01-01 2002-01-01
2003-01-01    13   2003-01-01 NULL       2002-01-01
2003-01-01    14   2002-01-01 NULL       2003-01-01
2003-01-01    15   2003-01-01 2002-01-01 NULL
2003-01-01    16   2002-01-01 2003-01-01 NULL
2003-01-01    21   2003-01-01 NULL       NULL
2003-01-01    22   NULL       2003-01-01 NULL
2003-01-01    23   NULL       NULL       2003-01-01
NULL          31   NULL       NULL       NULL

oh god, thank you sir! I spent so much time doing this heck of a monster formula that still gave me nulls and now I see the light at the end of the tunnel.
OH GOD I NEED 15 DATES, SAVE ME
M
Michael Freidgeim

Based on the ScottPletcher's solution from http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html I’ve created a set of functions (e.g. GetMaxOfDates3 , GetMaxOfDates13 )to find max of up to 13 Date values using UNION ALL. See T-SQL function to Get Maximum of values from the same row However I haven't considered UNPIVOT solution at the time of writing these functions

CREATE FUNCTION GetMaxOfDates13 (
@value01 DateTime = NULL,  
@value02 DateTime = NULL,
@value03 DateTime = NULL,
@value04 DateTime = NULL,
@value05 DateTime = NULL,
@value06 DateTime = NULL,
@value07 DateTime = NULL,
@value08 DateTime = NULL,
@value09 DateTime = NULL,
@value10 DateTime = NULL,
@value11 DateTime = NULL,
@value12 DateTime = NULL,
@value13 DateTime = NULL
)
RETURNS DateTime
AS
BEGIN
RETURN (
SELECT TOP 1 value
FROM (
SELECT @value01 AS value UNION ALL
SELECT @value02 UNION ALL
SELECT @value03 UNION ALL
SELECT @value04 UNION ALL
SELECT @value05 UNION ALL
SELECT @value06 UNION ALL
SELECT @value07 UNION ALL
SELECT @value08 UNION ALL
SELECT @value09 UNION ALL
SELECT @value10 UNION ALL
SELECT @value11 UNION ALL
SELECT @value12 UNION ALL
SELECT @value13
) AS [values]
ORDER BY value DESC    
)
END –FUNCTION
GO
CREATE FUNCTION GetMaxOfDates3 (
@value01 DateTime = NULL,  
@value02 DateTime = NULL,
@value03 DateTime = NULL
)
RETURNS DateTime
AS
BEGIN
RETURN dbo.GetMaxOfDates13(@value01,@value02,@value03,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
END –FUNCTION

D
DrYodo

You could create a function where you pass the dates and then add the function to the select statement like below. select Number, dbo.fxMost_Recent_Date(Date1,Date2,Date3), Cost

create FUNCTION  fxMost_Recent_Date 

( @Date1 smalldatetime, @Date2 smalldatetime, @Date3 smalldatetime ) RETURNS smalldatetime AS BEGIN DECLARE @Result smalldatetime

declare @MostRecent smalldatetime

set @MostRecent='1/1/1900'

if @Date1>@MostRecent begin set @MostRecent=@Date1 end
if @Date2>@MostRecent begin set @MostRecent=@Date2 end
if @Date3>@MostRecent begin set @MostRecent=@Date3 end
RETURN @MostRecent

END


M
M.A.Bell

Another way to use CASE WHEN

SELECT CASE true 
       WHEN max(row1) >= max(row2) THEN CASE true WHEN max(row1) >= max(row3) THEN max(row1) ELSE max(row3) end ELSE
       CASE true WHEN max(row2) >= max(row3) THEN max(row2) ELSE max(row3) END END
FROM yourTable

H
Hemendr

My solution can handle null value comparison as well. It can be simplified by writing as one single query but for an explanation, I am using CTE. The idea is to reduce the comparison from 3 number to 2 number in step 1 and then from 2 number to 1 number in step 2.

with x1 as
(
  select 1 as N1, null as N2, 3 as N3
  union
  select 1 as N1, null as N2, null as N3
  union
  select null as N1, null as N2, null as N3
)
,x2 as
(
select 
N1,N2,N3,
IIF(Isnull(N1,0)>=Isnull(N2,0),N1,N2) as max1,
IIF(Isnull(N2,0)>=Isnull(N3,0),N2,N3) as max2
from x1
)
,x3 as
(
 select N1,N2,N3,max1,max2,
 IIF(IsNull(max1,0)>=IsNull(max2,0),max1,max2) as MaxNo
 from x2
)
select * from x3

Output:

https://i.stack.imgur.com/XcI1W.png


g
gotqn

Finally, for the following:

SQL Server 2022 (16.x) Preview

Azure SQL Database

Azure SQL Managed Instance

we can use GREATEST, too. Similar to other T-SQL functions, here are few important notes:

if all arguments have the same data type and the type is supported for comparison, GREATEST will return that type;

otherwise, the function will implicitly convert all arguments to the data type of the highest precedence before comparison and use this type as the return type;

if one or more arguments are not NULL, then NULL arguments will be ignored during comparison; if all arguments are NULL, then GREATEST will return NULL;

The following types are not supported for comparison in GREATEST: varchar(max), varbinary(max) or nvarchar(max) exceeding 8,000 bytes, cursor, geometry, geography, image, non-byte-ordered user-defined types, ntext, table, text, and xml.


d
danvasiloiu

here is a good solution:

CREATE function [dbo].[inLineMax] (@v1 float,@v2 float,@v3 float,@v4 float)
returns float
as
begin
declare @val float
set @val = 0 
declare @TableVal table
(value float )
insert into @TableVal select @v1
insert into @TableVal select @v2
insert into @TableVal select @v3
insert into @TableVal select @v4

select @val= max(value) from @TableVal

return @val
end 

c
claudio

I do not know if it is on SQL, etc... on M$ACCESS help there is a function called MAXA(Value1;Value2;...) that is supposed to do such.

Hope can help someone.

P.D.: Values can be columns or calculated ones, etc.


Microsoft Access is a completely different product. Besides, are you able to source your claim of such a function? I have never seen or heard of this in Access.
MAXA is an Excel function, not Access.
B
Brijesh Ray

https://i.stack.imgur.com/peyNn.png

select  
 (select Max(salval) from( values (max(salary1)),(max(salary2)),(max(salary3)),(max(Salary4)))alias(salval)) as largest_val
 from EmployeeSalary

Running above query will give output as largest_val(10001)

Logic of above query is as below:

select Max(salvalue) from(values (10001),(5098),(6070),(7500))alias(salvalue)

output will be 10001


This is almost a copy of the solution posted in 29 jul '11 by @sven