[thelist] Custom sorting/ordering of query results [SOLVED!]

Frank lists at frankmarion.com
Tue Aug 23 09:14:51 CDT 2005

Found the solution!  Funny how letting something sit on the back burner of 
your mind pops up with interesting solutions. So here's the deal.

<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 

  , name
, FIND_IN_SET(name, '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 type="Learning MySQL" author="Frank Marion">

Do you use MySQL? It's surprisingly rich with features. Read it's manual, 
specifically it's function reference. Lots of solutions to odd problems can 
be found there.


At 09:38 AM 2005-08-23, you wrote:

>Man, some people really throw some curve balls at me.
>I've got an inner join query, that the client wants to be custom sorted.
>        t.product_type_id
>     ,  t.product_type
>     ,  p.product_type
>     ,  p.category_id
>     FROM
>       product_types t
>       INNER JOIN products p ON (t.product_type_id = p.product_type)
>     WHERE p.category_id=2
>     ORDER BY t.product_type_id;
>This works perfectly. Now the client wants it custom sorted, so that if 
>the query returns records   1, 2, 4, 5, 6 He wants to be able to sort it 
>at will (such as 6, 2, 4, 1, 5 --or whatever his momentary whim 
>is).  Basically, it's retrieving the name of a category, and he wants to 
>control the order in which it displays on the page.
>Is there a way of getting the result to match a certain order without 
>resorting to looping over a query several times? The approach I'm thinking 
>of is that of storing a list in a record, then using cfloop and bracket 
>notation #myQuery["columnName"][rowNum]# to loop over that list.
>I'm not having a lot of luck with this.
>Can anyone offer a different or better direction?

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

More information about the thelist mailing list