[thelist] Tip: Custom ordering of a query: correction

[Frank]

Sorry, the field should have been sorted by ID, not NAME.
Incorrect:   FIND_IN_SET(name, '6,2,3,4,1,5') as sort_column
Correct:      FIND_IN_SET(id, '6,2,3,4,1,5') as sort_column

<tip type="Custom ordering a query with MySQL" author="Frank Marion">

Controling the specific order of a query using a list with MySQL

I want to control the specific order in which a query returns it's results. 
If the records normally returned are in the order of A, B, C, D, E, I can 
use ORDER BY to sort on a particular column in acending or decending order. 
But I can also specify the exact order using MySQL's 
FIND_IN_SET(str,strlist) function. Imagine your table called "names" looks 
something like this:

id       | name
--------------------
1        | George
2        | Yuki
3        | Bob
4        | Fred
5        | Henry
6        | Rita

The client wants all the women's name first. What to do? What to do? Do as 
such:

SELECT
    id
  , name
, FIND_IN_SET(id, '6,2,3,4,1,5') as sort_column
FROM names
ORDER BY sort_column;

You query will be returned as: "Rita, Yuki, Bob, Fred, George, Henry".

Cool! Needless to say that we can also store lists as field type "set"., 
and thus retrieve the list of the DB. This is handy if you want your client 
to have total control over the order of things, such as the appearance of 
categories, or values in a select menu.

</tip>




Frank Marion     lists at frankmarion.com      Keep the signal high.