multiple ORDER BY clause

pablodelapena

Can MySQL order results by one thing and then once its done that order by something else?

For example, say I have a news board. I want to mark some topics as sticky and have them appear first. Can I have MySQL return all the news articles and push the ones marked as sticky to the top and order those by their timestamp, then just have the other articles order by their timestamp?

I can do this with multiple queries, I was just wondering if it were possible to do this all in one query?

TheDefender

SELECT * FROM table ORDER BY sticky, date
Something like this (depending on your field names) will order first by the sticky status, and then by date. (both in default ASC order)

SELECT * FROM table ORDER BY sticky ASC, date DESC
This would do sticky in ascending order and date in descending order.

(I think you get the idea from this)

By doing it like this, it will be in sticky, dated order, and the rest would then just be in non stickied dated order.

pablodelapena

that easy huh?

How can I have it so that stickies are ordered by their timestamp tho. ie: The field sticky will be boolean, either true or false.

Will they be ordered in this way?

TheDefender

Well, if you are using true,false boolean, then you would want to do a descending order on the sticky field (Since true comes after false alphabetically). Also, descending with the date will give you the newest to the oldest. Since you'd order the order by clauses as sticky then date, it will first order by sticky (All trues in newest to oldest date order) and then by date (all falses in newest to oldest date order) I don't know how you plan to store your date, but I recommend storing it in UNIX Timestamp format (seconds since the Epoch).

Your query would go something like this:
SELECT * FROM table ORDER BY sticky DESC, date DESC

Roger_Ramjet

And I would say 'AVOID UNIX TIMESTAMP LIKE THE PLAGUE'. Apart from anything else, ver 4.1 and up use the datetime format for timestamp columns anyway. So you future proof your code by starting out with datetime even if you are not using mysql 4.1 now.

UNIX timestamps may have some advantages of 'universality' across systems, but they are a pain to work with. If you are storing them in a db then using native date types allows you to use the inbuilt date functions in your sql.

TheDefender

Bah... They aren't that much of a pain to work with if you are familiar with the various date manipulation functions in PHP... It's more of a preference thing (or how you learn), because I would rather pull raw data first (personally), and manipulate it in PHP instead of taking advantage of built-in MySQL date functions. Whatever works for you.

Roger_Ramjet

And I would rather do everything I can as fast as I can which means SQL not script. But then I am comfortable with both.

For those of you who don't know, SQL benchmarks out at 10 - 20 times faster than any and every programming language when processing multiple records.

pablodelapena

thanks guys, i get you now. Cheers for the adivce.

As far as Unix and datetime. Is it odd that i find it funny that I actually use both in my tables? One for ordering etc(datetime) and the other to work out how many seonds have past since the enws bulletin was added so Ic an display as posted x minutes/hours/days ago.

Talking of which, can SQL do this instead?

Roger_Ramjet

Yes , of course it can. And you don't have to waste valuable brain-space remembering how many seconds there are in a day, week, or month. TheDefender is just displaying a prime example of Knurdishness: the ability to be proud of possesing esoteric knowledge that complicates an otherwise simple issue. It's like the Masons and their secret signs.

Just imagine you want members to input their birthdate; no one born before 1970 would be able to join. So anyone who recomends UNIX timestamps is recomending that you join a secret society where membership is based upon awkwardness and stupidity.

Roger_Ramjet

There is an interesting thread in here somewhere about the variouse time formats for asll the diff operating systems and when they will fail, and UNIX is the second, after MS DOS 8-bit times.

TheDefender

Hey Roger, I am not 100% sure when I jumped into the "I wanna be flamed for pride" category. I don't appreciate the stupid implication either. I am not pushing my own agenda, and in fact, concluded my last post with "Whatever works for you." so I'll just bow out of this post instead of getting into a battle of wits with someone who would obviously rather belittle people with his chest poked out. I am above that.

Roger_Ramjet

And I was out of order with my post. My apologies.

TheDefender

No sweat. It just rubbed me the wrong way this morning before I could grab my first cup of coffee... (That'll teach me).

You've been around long enough... 3 Things you should know about me by now Roger...
1) I always like to avoid confrontations whenever possible.
2) I am always open to learning new things while giving my own opinions. (Just ask LordShryku. He has put me in my place more than once).
3) I always make it clear (when there is more than one way to do something) that it is how I would personally do it. I don't send my own word out as Gospel, because more often than not, it isn't.

It's all good. No hard feelings.

pablodelapena

whoo, heated...

Ok, so if SQL can do it? how would I go about doing it? And can it all be done with one query?

I apologise if it alls eems obvious, but my SQL knowledge extends to, Select, Update, Insert and Delete with functions such as count() and now()... sorry for my noobishness, but we all elarn from the bottom!