SQL MAX of multiple columns

Question

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

User · Answer

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

User · Answer

Above table is an employee salary table with salary1 salary2 salary3 salary4 as columns Query below will return the max value out of four columns  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

User · Answer

If you re using MySQL  you can use  SELECT GREATEST col1  col2      FROM table

User · Answer

Using CROSS APPLY  for 2005         SELECT MostRecentDate  FROM SourceTable     CROSS APPLY  SELECT MAX d  MostRecentDate FROM  VALUES  Date1    Date2    Date3   AS a d   md

User · Answer

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

User · Answer

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  gt   date2                               AND date1  gt   date3 THEN date1                          WHEN date2  gt   date1                               AND date2  gt   date3 THEN date2                          WHEN date3  gt   date1                               AND date3  gt   date2 THEN date3                          ELSE date1                     END AS MostRecentDate           FROM      data table           AS date query

User · Answer

For T-SQL  MSSQL 2008    SELECT    SELECT      MAX MyMaxName      FROM   VALUES               MAX Field1                  MAX Field2              MyAlias MyMaxName       FROM MyTable1

User · Answer

If you re using MySQL  you can use  SELECT GREATEST col1  col2      FROM table

User · Answer

Please try using UNPIVOT   SELECT MAX MaxDt  MaxDt    FROM tbl  UNPIVOT     MaxDt FOR E IN         Date1  Date2  Date3   AS unpvt

User · Answer

Well  you can use the CASE statement   SELECT     CASE         WHEN Date1  gt   Date2 AND Date1  gt   Date3 THEN Date1         WHEN Date2  gt   Date1 AND Date2  gt   Date3 THEN Date2         WHEN Date3  gt   Date1 AND Date3  gt   Date2 THEN Date3         ELSE                                        Date1     END AS MostRecentDate    For Microsoft SQL Server 2008 and above  you may consider Sven s simpler answer below

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  coalesce Date2  0001-01-01   AND Date1  gt  coalesce Date3  0001-01-01   THEN Date1          WHEN Date2  gt  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

User · Answer

Above table is an employee salary table with salary1 salary2 salary3 salary4 as columns Query below will return the max value out of four columns  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

User · Answer

Using CROSS APPLY  for 2005         SELECT MostRecentDate  FROM SourceTable     CROSS APPLY  SELECT MAX d  MostRecentDate FROM  VALUES  Date1    Date2    Date3   AS a d   md

User · Answer

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 gt  Date2          IIF  Date1 gt  Date3  Date1  Date3           IIF  Date2 gt  Date3  Date2  Date3   AS MostRecentDate

User · Answer

Well  you can use the CASE statement   SELECT     CASE         WHEN Date1  gt   Date2 AND Date1  gt   Date3 THEN Date1         WHEN Date2  gt   Date1 AND Date2  gt   Date3 THEN Date2         WHEN Date3  gt   Date1 AND Date3  gt   Date2 THEN Date3         ELSE                                        Date1     END AS MostRecentDate    For Microsoft SQL Server 2008 and above  you may consider Sven s simpler answer below

User · Answer

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

User · Answer

SELECT      CASE          WHEN Date1  gt   Date2 AND Date1  gt   Date3 THEN Date1          WHEN Date2  gt   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

User · Answer

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

User · Answer

SELECT      CASE          WHEN Date1  gt   Date2 AND Date1  gt   Date3 THEN Date1          WHEN Date2  gt   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

User · Answer

Well  you can use the CASE statement   SELECT     CASE         WHEN Date1  gt   Date2 AND Date1  gt   Date3 THEN Date1         WHEN Date2  gt   Date1 AND Date2  gt   Date3 THEN Date2         WHEN Date3  gt   Date1 AND Date3  gt   Date2 THEN Date3         ELSE                                        Date1     END AS MostRecentDate    For Microsoft SQL Server 2008 and above  you may consider Sven s simpler answer below

User · Answer

Well  you can use the CASE statement   SELECT     CASE         WHEN Date1  gt   Date2 AND Date1  gt   Date3 THEN Date1         WHEN Date2  gt   Date1 AND Date2  gt   Date3 THEN Date2         WHEN Date3  gt   Date1 AND Date3  gt   Date2 THEN Date3         ELSE                                        Date1     END AS MostRecentDate    For Microsoft SQL Server 2008 and above  you may consider Sven s simpler answer below

User · Answer

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

User · Answer

For T-SQL  MSSQL 2008    SELECT    SELECT      MAX MyMaxName      FROM   VALUES               MAX Field1                  MAX Field2              MyAlias MyMaxName       FROM MyTable1

User · Answer

Another way to use CASE WHEN   SELECT CASE true         WHEN max row1   gt   max row2  THEN CASE true WHEN max row1   gt   max row3  THEN max row1  ELSE max row3  end ELSE        CASE true WHEN max row2   gt   max row3  THEN max row2  ELSE max row3  END END FROM yourTable

User · Answer

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 gt  MostRecent begin set  MostRecent  Date1 end if  Date2 gt  MostRecent begin set  MostRecent  Date2 end if  Date3 gt  MostRecent begin set  MostRecent  Date3 end RETURN  MostRecent   END

User · Answer

If you re using MySQL  you can use  SELECT GREATEST col1  col2      FROM table

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   date2                               AND date1  gt   date3 THEN date1                          WHEN date2  gt   date1                               AND date2  gt   date3 THEN date2                          WHEN date3  gt   date1                               AND date3  gt   date2 THEN date3                          ELSE date1                     END AS MostRecentDate           FROM      data table           AS date query

User · Answer

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

User · Answer

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  gt   date2                               AND date1  gt   date3 THEN date1                          WHEN date2  gt   date1                               AND date2  gt   date3 THEN date2                          WHEN date3  gt   date1                               AND date3  gt   date2 THEN date3                          ELSE date1                     END AS MostRecentDate           FROM      data table           AS date query

User · Answer

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

User · Answer

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 gt  MostRecent begin set  MostRecent  Date1 end if  Date2 gt  MostRecent begin set  MostRecent  Date2 end if  Date3 gt  MostRecent begin set  MostRecent  Date3 end RETURN  MostRecent   END

User · Answer

Problem  choose the minimum rate value given to an entity Requirements  Agency rates can be null   MinRateValue     CASE     WHEN ISNULL FitchRating RatingValue  100   lt    ISNULL MoodyRating RatingValue  99      AND  ISNULL FitchRating RatingValue  100   lt    ISNULL StandardPoorsRating RatingValue  99      THEN FitchgAgency RatingAgencyName     WHEN ISNULL MoodyRating RatingValue  100   lt    ISNULL StandardPoorsRating RatingValue   99     THEN MoodyAgency RatingAgencyName     ELSE ISNULL StandardPoorsRating RatingValue   N A    END    Inspired by this answer from Nat

User · Answer

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

User · Answer

Please try using UNPIVOT   SELECT MAX MaxDt  MaxDt    FROM tbl  UNPIVOT     MaxDt FOR E IN         Date1  Date2  Date3   AS unpvt

User · Answer

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

User · Answer

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  gt  coalesce Date2  0001-01-01   AND Date1  gt  coalesce Date3  0001-01-01   THEN Date1          WHEN Date2  gt  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

User · Answer

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

User · Answer

Another way to use CASE WHEN   SELECT CASE true         WHEN max row1   gt   max row2  THEN CASE true WHEN max row1   gt   max row3  THEN max row1  ELSE max row3  end ELSE        CASE true WHEN max row2   gt   max row3  THEN max row2  ELSE max row3  END END FROM yourTable

User · Answer

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

User · Answer

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 gt  Date2          IIF  Date1 gt  Date3  Date1  Date3           IIF  Date2 gt  Date3  Date2  Date3   AS MostRecentDate

User · Answer

If you re using MySQL  you can use  SELECT GREATEST col1  col2      FROM table

User · Answer

Problem  choose the minimum rate value given to an entity Requirements  Agency rates can be null   MinRateValue     CASE     WHEN ISNULL FitchRating RatingValue  100   lt    ISNULL MoodyRating RatingValue  99      AND  ISNULL FitchRating RatingValue  100   lt    ISNULL StandardPoorsRating RatingValue  99      THEN FitchgAgency RatingAgencyName     WHEN ISNULL MoodyRating RatingValue  100   lt    ISNULL StandardPoorsRating RatingValue   99     THEN MoodyAgency RatingAgencyName     ELSE ISNULL StandardPoorsRating RatingValue   N A    END    Inspired by this answer from Nat

User · Answer

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

User · Answer

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  gt   date2                               AND date1  gt   date3 THEN date1                          WHEN date2  gt   date1                               AND date2  gt   date3 THEN date2                          WHEN date3  gt   date1                               AND date3  gt   date2 THEN date3                          ELSE date1                     END AS MostRecentDate           FROM      data table           AS date query

User · Answer

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

User · Answer

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

[sql] SQL MAX of multiple columns?

Examples related to sql

Examples related to sql-server

Examples related to tsql