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:318526 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL query sorting problem
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
Re: SQL query sorting problem
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
Re: SQL query sorting problem
that's very neat, chris! i didn't think one could use FIND_IN_SET in ORDER BY clause - live and learn! Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Chris Blackwell wrote: 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:318578 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL query sorting problem
Michael, Thanks. That worked perfectly. Jim - Original Message - From: Dawson, Michael m...@evansville.edu To: cf-talk cf-talk@houseoffusion.com 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
Re: SQL query sorting problem
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
