Custom MYSQL Custom Sort Order

Question

I have a simple media gallery that sorts the albums based on date as seen in the code example. In amongst the albums I have a "Trash" album for deleted media. How do I keep the ORDER BY albums.date DESC but always set the "Trash" album to be the very last in the order? The album is in column "title" and is always called "trash".

Thanks for your help...

$query = mysql_query("SELECT albums.*,photos.path FROM albums LEFT JOIN photos ON albums.albumCover=photos.id WHERE albums.user='$siteUserID' ORDER BY albums.date DESC");

Check out this question to: stackoverflow.com/questions/3180032/… — McKracken
– McKracken, Commented Sep 27, 2012 at 2:13

John Woo · Accepted Answer · 2012-09-27 02:09:53Z

2

you can take advantage of the CASE on ORDER BY clause

SELECT... FROM... WHERE.. ORDER BY (CASE WHEN albums.title = 'TRASH' THEN 1 ELSE 0 END ) ASC, albums.date DESC

answered Sep 27, 2012 at 2:09

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Woo Over a year ago

@xtiaan in the order by clause, it test the value of the title whether is it a trash or not, if it is trash it sets a virtual value for the row to 1 otherwise 0 and then sorts them in ascending order. after that, it then sorts the albums.date in descending order.

Michael Berkowski · Accepted Answer · 2012-09-27 02:10:11Z

You will need to use an ORDER BY CASE which assigns a higher number to the Trash values, hence sorting them later than others. Values not Trash get a zero, and Trash gets a 1. The zeros sort first!

SELECT albums.*, photos.path FROM albums LEFT JOIN photos ON albums.albumCover=photos.id WHERE albums.user='$siteUserID' ORDER BY CASE WHEN albums.title = 'Trash' THEN 1 ELSE 0 END, /* Then suborder the zeros and ones by date */ albums.date DESC

Collectives™ on Stack Overflow

Custom MYSQL Custom Sort Order

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related