>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?
You can do this in a very easy and elegant manner with MySQL. No need for join or nasty case statements SELECT * FROM cities WHERE state IN (<cfqueryparam value="#states#" list="yes">) ORDER BY FIND_IN_SET(state, <cfqueryparam value="#states#">), city NB. the second cfqueryparam is not a list, its just a comma seperated string like "NY,CA,FL" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:318543 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
