Topic awaiting preservation: ORDER BY from two different fields?

Greets!

I have a MySQL db that has a "release_date" and an "updated_date" field. I'd like to retrieve the info based on which field is more recent, on a per row basis.

For instance, I have an article release Monday, but updated Wednesday. I also have an article released Tuesday, and one Wednesday. How do I order them so that they are in order by most recent date (regardless of whether it's release_date or updated_date)?

Can this be done with the ORDER BY option right in the query?

Right now I've got an ORDER BY RELEASE_DATE line, but I'd like to expand on it so that the most recent info is in the list.

Thanks!

Not sure off the top of my head, but when would the release_date ever be later than the updated_date?

-jiblet

Ah, quite simple really.

ORDER BY GREATEST(release_date, updated_date)

-jiblet

Okay...this works well, but I need to throw something into the mix here. I've been playing with it and can't seem to get it to work right.

Now, lets say I have release_date and it's partner, release_time, as well as updated_date and it's brother, updated_time. I need to sort them by which ever is most recent, using the release variables together, and the updated variables together. I hope this makes sense.

Ideas?



[This message has been edited by Pugzly (edited 01-28-2002).]

hmm.. Is there a reason you are seperating out the update_date and update_time? I'm assuming that these fields get updated simultaneously (one with the date and one with the time) so I'm wondering why you are not using a datetime field and just consolidating the two.



:[ Computers let you make more mistakes faster than any other invention in human history, with the possible exceptions of handguns and tequila. ]:

Actually, that's a boo-boo on our part. We originally just wanted the date, so the original database field was just the date. Later, we decided to add the time, and in order to not break the existing content and applications, added another field for the time.

I COULD go back and change this to a regular timestamp if that would be easier. I was thinking about that earlier today. I would probably just create a new field from the date and time fields, and once I was happy with the format, could change the applications to use this new field. The date field is currently in the correct "yyyy-mm-dd" format. We have several queries at this field, some of which get printed after running through a function to convert it to a "human friendly" format.



[This message has been edited by Pugzly (edited 01-28-2002).]