Order by with two date fields - possible with mysql?

Hi all,

this might probably a dumb question, so please bare with me.

I am doing a SELECT on a table which has two date fields. One for the create date and one for the startdate.

I output my results in chronological order with:

...ORDER BY startdate DESC, crdate DESC ...

Startdate is not required field so it might also be empty. The result might be incorrect because of this.

What I want to do is: use the latest date for each dataset Startdate or Createdate.

example:

dataset 1
created: 01-01-2008
startdate:
-> Should use created.

dataset 2
created: 03-01-2008
startdate: 02-02-2008
-> Should use startdate.

dataset 3
created: 14-01-2008
startdate: 16-01-2008
-> Should use startdate.

List should look like this:

1. dataset 2
2. dataset 3
3. dataset 1

I don't know however how I can do this with mysql. I hope I could make myself clear!

Any hint is greatly apreciated!

Thanks and kind regards

Sven

ORDER BY GREATEST(startdate,COALESCE(startdate,crdate)) DESC

Hi there,

thanks for the reply!

Unfortunately this didn't change a thing. I read through the description of COALESCE option and it sounds like it's what I need.

I have a very simple query like this:

SELECT * FROM db_jpp ORDER BY GREATEST(starttime,COALESCE(starttime,crdate)) DESC

However jobs with no starttime (value of the field is 0) are being listed at the very end. The rest is ordered correctly.

I also tried something like this:

SELECT * FROM db_jpp ORDER BY COALESCE(starttime,crdate) DESC

also no change :/

btw. it's linux timestamps.

any idead why this might happen!?

Kind regards

Sven

OMG! my fault! I should have read more carefully.

COALESCE returns the first NONE NULL!!! value. which would be fitting for 0.

using only GREATEST is working like a charm!

thanks for the help!

Kind regards

Sven

so when you said "Startdate is not required field so it might also be empty" what did you mean by "empty"?

obviously if COALESCE didn't do the job for you, then the "empty" values weren't NULL, they were something else????

yes, sorry that was my fault. The fields aren't actually empty.

They have a linux timestamp or they are default '0'. So not empty.

Sorry.

don't worry, i am just glad you got your solution