MySQL Bubble Results Trick
I have discovered a cool MySQL trick that allows you to order a result set by a specified list - before ordering it by column X, Y or Z etc.
Here’s an example….
If I had a table called “user” with the following contents;
| ID | Name | Age |
|---|---|---|
| 1 | James | 24 |
| 2 | Matthew | 26 |
| 3 | Richard | 30 |
| 4 | Colin | 30 |
And I did;
1 | SELECT name FROM user ORDER BY name ASC |
I would get;
Colin
James
Matthew
Richard
If I wanted “Richard” to appear first, I could do;
1 | SELECT name FROM user ORDER BY, FIND_IN_SET(id, 3) DESC, name ASC |
Or even;
1 | SELECT * FROM user ORDER BY, FIND_IN_SET(name, "Richard") DESC, name ASC |
The magic happens using the “FIND_IN_SET” function (details here; http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set).
FIND_IN_SET(str, strlist) is a String function that returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters.
A more practical example would be if you had a table of classified adverts and wanted to show those that belong to a certain category first, e.g.;
1 | SELECT * FROM classified ORDER BY, FIND_IN_SET(categoryId, 1) DESC, dateAdded DESC |
This would show the classified ads in order of date (dateAdded) but with those from categoryId “1″ first.