ORDER BY column ASC but...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

ORDER BY column ASC but...

Post by alex.barylski »

Here is my query:

[sql]SELECT * FROM $table ORDER BY $column $direction LIMIT $offset, $count[/sql]

My $table has a field in it called time which is how the table above is usually sorted. Everything works, except that I am trying to order the oldest records first, basically implementing a FIFO (first in, first out) stack. However easy this, I *also* need to have any records with a timestamp of ZERO to be pushed to the bottom as that indicates lowest priority and shouldn't be pushed to the top of the stack even though technically, they are the oldest records...

Make sense? Can it be done while still showing all the records? Do I have to use sub-queries - hopefully there is another technique.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: ORDER BY column ASC but...

Post by Kieran Huggins »

what if the zeroed timestamps are NULL instead? I seem to recall them coming first for some reason in that case.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: ORDER BY column ASC but...

Post by alex.barylski »

Kieran Huggins wrote:what if the zeroed timestamps are NULL instead? I seem to recall them coming first for some reason in that case.
Hmmm...maybe I could try that...although I need records with a NULL timestamp to come *last* not first... :?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: ORDER BY column ASC but...

Post by Kieran Huggins »

Sorry, that IS what I meant. I think NULL sorts high, not low.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: ORDER BY column ASC but...

Post by alex.barylski »

Kieran Huggins wrote:Sorry, that IS what I meant. I think NULL sorts high, not low.
Only one way to find out I guess. Lemme know how it goes, eh? :D :P

Just kidding... :lol:
Post Reply