Find the largest date

[feshangi]

Let's say I have a table which has records like this:

ID Date1   Date2   Date3   Date4   OtherFields...
1  2/23/03 4/15/03 3/11/03 3/18/05 ..............
2  2/24/03 2/10/05 4/12/03 3/15/03 ..............
3  NULL    3/12/03 2/11/03 3/31/03 ..............

Is it possible to find the largest date (Date1...Date4) for each ID within the query and use AS to give a column name associated with that ID?

Note: I tried MAX() and it didn't like the NULL!


Can anyone please help?

Thanks,

Mike

 

[PLGREEN]

You could try the following:

Max(IsNull(Date1, '01/01/1900'))

Patrick Green

 

[SQLSister]

Odd, I can use max and it ignores nulls. Or did you want the largest of Date1, date2, date3, date4 from each individual record? Max would not get you that in any event. If you are going to do this you really should create a related table for storing the dates as the problem is your structure is not conducive to querying like that.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[feshangi]

Thank you guys for the replys.

SQLSister, you are correct. I meant the largest of date1, date2, date3, and date4 from each individual record.

 

[PLGREEN]

Use a case statement...

'MaxDate' =
Case
When IsNull(Date4, '01/01/1900') > IsNull(Date3, '01/01/1900') and IsNull(Date4, '01/01/1900') > IsNull(Date2, '01/01/1900') and IsNull(Date4, '01/01/1900') > IsNull(Date1, '01/01/1900') then Date4
When IsNull(Date3, '01/01/1900') > IsNull(Date4, '01/01/1900') and IsNull(Date3, '01/01/1900') > IsNull(Date2, '01/01/1900') and IsNull(Date3, '01/01/1900') > IsNull(Date1, '01/01/1900') then Date3
When IsNull(Date2, '01/01/1900') > IsNull(Date4, '01/01/1900') and IsNull(Date2, '01/01/1900') > IsNull(Date3, '01/01/1900') and IsNull(Date2, '01/01/1900') > IsNull(Date1, '01/01/1900') then Date2
ELSE DATE1
END,

Thanks,
Patrick Green

 

[SQLSister]

{code]select case when isnull(date1, '1/1/1900')>=isnull(date2, '1/1/1900')
and isnull(date1, '1/1/1900')>=isnull(date3, '1/1/1900')
and isnull(date1, '1/1/1900')>=isnull(date4, '1/1/1900') then date1
when isnull(date2, '1/1/1900')>=isnull(date3, '1/1/1900')
and isnull(date2, '1/1/1900')>=isnull(date4, '1/1/1900')
and isnull(date2, '1/1/1900') >= isnull(date1, '1/1/1900') then date2
when isnull(date3, '1/1/1900')>=isnull(date4, '1/1/1900')
and isnull(date3, '1/1/1900') >= isnull(date1, '1/1/1900')
and isnull(date3, '1/1/1900') >= isnull(date2, '1/1/1900') then Date3
when isnull(date4, '1/1/1900')>=isnull(date3, '1/1/1900')
and isnull(date4, '1/1/1900') >= isnull(date2, '1/1/1900')
and isnull(date4, '1/1/1900') >= isnull(date1, '1/1/1900') then Date4 end from table1[/code]

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[SQLSister]

Patrick, the problem with your query is that it will not work correctly if you happen to have the same date value in two of the fields. That is why I used >=.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[PLGREEN]

Good catch. Sorry abou that "bad" post.

 

[SQLSister]

No you had the right idea, but stuff like this gets more and more complex, one reason why this data structure is to be avoided. It's all so simple if you have a related table.

But don't let that discourage you from posting more solutions as you clearly have a good understanding of some of the more complex SQL structures. believe me we all miss little things like that from time to time.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[r937]

select greatest(Date1,Date2,Date3,Date4) as maxdate from mytable

conforms to Core SQL-99 standard

wait, is this supported in sql server?

oh well

it's also worth noting that this can also be handled easily in the scripting language

see thread232-1001565




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[feshangi]

I got this to work after outputing the data:

Please see my other post under coldfusion:

http://www.tek-tips.com/viewthread.cfm?qid=1001565

Thanks everyone for all the suggestions


Mike