SQL MAX of multiple columns?

Question

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

TableName [Number, Date1, Date1, Date3, Cost]

I need to return something like this: [Number, Most Recent Date, Cost]

Query?

Answer 1

Is this homework?

I would use MAX on a CASE to get the maximum of the columns. How many columns are we talking about? Is Number a primary key?

Answer 2

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

Answer 3

I do not understand if you mean you want max of each:

Select max(Number) as maximumnumber , max(Date1) as mostrecentdate, max(Cost) as maxcost
from TableName

or if you want number and cost of the row with the mostrecentdate

select top 1 number, date1, cost
from TableName
order by date1 desc

Answer 4

Either of the two samples below will work:

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

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

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

Answer 5

If you're using MySQL, you can use

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

Answer 6

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

Answer 7

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

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