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 such: SELECT id , 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> <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. </tip> 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. > > SELECT DISTINCT > 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.