Order by greater of two date columns

[Wysocki]

I have a file that has two date fields in it. The first date is always there but the second date field may or may not have a value, like this:

A 2005-01-29 2004-10-15
B 2004-09-19 0000-00-00
C 2004-12-31 0000-00-00
D 2005-01-02 2004-11-11

I want to select the data ORDER BY the first date UNLESS the second date is present, then I want it to use that date for that row instead. So after sorting, the order above would be BADC. How do I code the ORDER BY for this kind of sort?

[technobard]

You're talking about running SQL against a flat file? The only database I know of that can handle that is Oracle using "external" to make the outside file appear to be an internal table. Is that what you're using? If so, you can probably use a decode on the second date.

Something like:

select .... from external_table 
order by decode(second_date, '0000-00-00', first_date, 
second_date)

Decode is basically an "if" statement, so if you're not using Oracle, (and decode is not available) you can use whatever the language allows for conditionals. This may or may not be supported in the order by clause for other DBMSs, but Oracle does support it.

The decode above translates as:
if (second_date == '0000-00-00') {
return first_date;
} else {
return second_date;
}

[Wysocki]

Sorry to mislead you, but it's a MySql database being accessed with a php webpage. I'll give the "decode" function a try anyway.

[technobard]

MySQL doesn't have a decode. That's an Oracle specific function. I believe MySQL does support an if statement, but I'd have to look at the syntax to see if it could do what you want.

[Wysocki]

SELECT * FROM  `recvitem` 
ORDER BY if('0000-00-00'==shipdate,recvdate,shipdate)

Just tried the if() function above but this gives me a syntax error. Not sure why though.

[EDIT:] Son of a gun, I just can't seem to remember the syntax differences between PHP and MySql. The double equal sign got me! Now it works, thanx for your help!