CODEWITHSUNDEEP
phpmysqlsql-order-by
TheMeisterSE
TheMeisterSE

Reputation: 541

Select and sort on two columns MySQL

So, my code looks like this

mysql_query("SELECT * FROM threads WHERE forum_id = '$id' ORDER BY type,posted DESC") or                                 
die(mysql_error());

"posted" is the value of time() and is selected decreased to put the latest one at the top. "type" contains either 1 or 2. If it's 2, the thread is pinned. Currently, it sorts after posted but those which should be at the top (pinned) are at the bottom. Is there any fix that I'm missing?

Upvotes: 2

Views: 126

Answers (2)

SeanWM
SeanWM

Reputation: 16989

Try: ORDER BY type DESC, posted DESC

By default, it sorts ascending. You need to specify the order for both fields you would like to order by.

ORDER BY Documentation

Upvotes: 3

Marc B
Marc B

Reputation: 360572

Don't forget that the asc/desc in an order by applies to each individual field, not the entire order by clause. 

ORDER BY type DESC, posted ASC

MySQL can also accept arbitrary logic for order by, so for more complicated sort requirements, you could have something like

ORDER BY IF(type=2, 0, 1) AS pinned ASC, posted DESC

as long the arbitrary logic returns something that's trivially sortable (e.g. a number or a string), there's no limit to how complicated the sorting can be.

Upvotes: 2

Related Questions