[SOLVED] ORDER BY date & if pinned sort first?

[Presto-X]

I was not sure the best way to go about this, I have a query that I want to sort by date, and by one other field called pinned. This is going to be for a blog so if posts are pinned I want them to be sorted at the top by date, then the rest of the posts sorted after these by date.

 

$query = "SELECT id, title, date, content, pinned FROM `zen_blogs` ORDER BY `date` DESC";

 

I have the blog sorting now by date using ORDER BY date. I was not sure how to do the other part, any help or suggestions are more then welcome thanks for all of your help guys.

[ShoeLace1291]

Is the id field auto_increment?  If it is, if you order by id desc then it would be the same thing as order by date.

[lur]

"You can sort on multiple columns, and you can sort different columns in different directions. For example, to sort by type of animal in ascending order, then by birth date within animal type in descending order (youngest animals first), use the following query:"

mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;

 

http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

[fanfavorite]

ORDER BY Date will only work if you made the mysql field a date field.  If not, use this ORDER BY str_to_date(Date, '%c/%e/%Y')".  Where Date is the field name and its in the format of M/D/Y.  There are other formats using this method, just look around. 

[Presto-X]

Hey guys thanks for the replys,

 

The following works just fine

ORDER BY date DESC

 

Now if I add , pinned after date but befor DESC like so:

 

ORDER BY date, pinned DESC

 

It now longer sorts it? So I'm guessing this is not going to be as easy as I was hoping. Really I do not need to sort by the date field like Hybrid Kill3r mentioned I could use order by id, thats not a problem it's how to sort by the second part that I can not get to work. I want the pinned to come before all of the other posts.

[fanfavorite]

ORDER BY date DESC,pinned ASC

[Presto-X]

Thanks fanfavorite, I think it was the space after the , that was messing up everying this is the code that I ended up using:

 

$query = "SELECT id, title, date, content, pinned FROM `zen_blogs` ORDER BY pinned DESC,id DESC";

Thanks again guys for the replys