return max across columns

[Earth]

Hi,

I have a table that looks like this:

ID Date1 Date2 Date3 Date4
-- ----- ----- ----- -----
1 1 Jan 2 Jan 3 Jan 4 Jan
2 10 Feb 8 Feb 6 Feb 4 Feb

I want to return:
Each ID, and the max date across the four date columns.
eg.
ID DateMax
-- -------
1 4 Jan
2 10 Feb

I'm after any direction on how I best do this with SQL!

Thanks in advance, rather urgent.

 

[karluk]

I would try something like

select id, max(MaxDate) as DateMax from
(select id, max(Date1) as MaxDate from your table
group by id
union
select id, max(Date2) as MaxDate from your table
group by id
union
select id, max(Date3) as MaxDate from your table
group by id
union
select id, max(Date4) as MaxDate from your table
group by id)
group by id

 

[tlbroadbent]

Here are two possibilities. The first should be more efficient.

Use the CASE function:
Select
ID,
MaxDate=Case
When Date1>=Date2 And Date1>=Date3 And Date1>=Date4
Then Date1
When Date2>=Date3 And Date2>=Date4
Then Date2
When Date3>=Date4
Then Date3
Else Date4 End
From YourTable

Use a UNION subquery and aggregate function:
Select ID, DateMax=Max(Date1)
From (
Select
ID, Date1
From YourTable
Union
Select
ID, Date2
From YourTable
Union
Select
ID, Date3
From YourTable
Union
Select
ID, Date4
From YourTable) As qry
Group By ID Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.