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:318526
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
