Michael, Thanks. That worked perfectly.
Jim ----- Original Message ----- From: "Dawson, Michael" <[email protected]> To: "cf-talk" <[email protected]> Sent: Monday, January 26, 2009 8:07 AM Subject: RE: SQL query sorting problem > 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:318593 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
