Mike, the way i read OP's question is that the order of states is not fixed, but user-defined: i.e. a user selects several states in several select lists and the data returned must be in the order of selected states. but in case of pre-defined order of states, your suggestion will be perfect.
Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Dawson, Michael wrote: > The proper solution would be a separate table that contains the state > codes with their desired sort order. That table is joined to the main > table. This is probably best if you have to list all 50+ states. > > If you only need to list three states, then I would use CASE as Azadi > suggested. > > In MSSQL: > > SELECT > state > ,CASE > WHEN state = 'NY' THEN 1 > WHEN state = 'CA' THEN 2 > WHEN state = 'FL' THEN 3 > END AS stateSortOrder > FROM > cities > ORDER BY > stateSortOrder > > > mike > > 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:318577 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
