rty a UNION query generated inside a <cfloop>: <cfquery ...> <cfloop from="1" to="#listlen(states)#" index="j"> (SELECT *, #j# AS sortcol FROM cities WHERE state = '#listgetat(states, j)#') <cfif j lt listlen(states)>UNION </cfif> </cfloop> ORDER BY sortcol, city </cfquery>
you could also probably build a dynamic CASE statement instead of using a UNION query, but i will need to check up the syntax of that... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Jim McAtee wrote: > DBMS is MySQL 5. I have a report that is generated for cities within > several states. The states are designated by their two letter postal > abbreviations and are in a certain order. For example: > > 1. NY > 2. CA > 3. FL > > I would like the records of the report sorted by the original state order, > then by city name. I can pull the data for the report in single query, > but can't figure out how to maintain the state order. > > Turning the state list above into a comma delimmited list (NY,CA,FL), my > query looks something like: > > SELECT * > FROM cities > WHERE state IN (<cfqueryparam value="#states#" list="yes">) > ORDER BY state, city > > Except that I lose the original order of the state list. > > There are a number of workarounds, such as looping through the states and > doing one query for each. Or run the single query shown above and then in > a similar manner loop through the states and do a QoQ for each. > > Is there a way to do this in a single query? > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:318511 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
