[thelist] Custom sorting/ordering of query results

[Frank]

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.