Closed Thread Icon

Topic awaiting preservation: ORDER BY from two different fields? (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=11960" title="Pages that link to Topic awaiting preservation: ORDER BY from two different fields? (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: ORDER BY from two different fields? <span class="small">(Page 1 of 1)</span>\

 
Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

posted posted 12-19-2001 16:26

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!

jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 12-19-2001 16:48

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

-jiblet

jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 12-19-2001 17:04

Ah, quite simple really.

ORDER BY GREATEST(release_date, updated_date)

-jiblet

Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

posted posted 01-28-2002 17:19

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).]

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 01-28-2002 18:13

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. ]:

Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

posted posted 01-28-2002 20:55

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).]

« BackwardsOnwards »

Show Forum Drop Down Menu