ORDER BY two columns ?

this post is not archived.
 >> Stay informed about: ORDER BY two columns ? 

usenet.DeleteThis@isotopeREEMOOVEmedia.com wrote:

 > I'm trying to figure out how I can sort records alphabetically by
 > almost "blending" two fields.
 >
 > SELECT city, county
 > FROM fullist
 > WHERE type = 'city' or type = 'county'
 > ORDER BY ???;
 >
 > It should output like this :
 >
 > ----------------------------
 > | city | county | type |
 > ----------------------------
 > | A | x | city |
 > | x | B | county |
 > | C | x | city |
 > | x | D | county |
 > ----------------------------
 >
 > "Type" indicates which of the fields should be used for the sort. I'm
 > thinking that a JOIN might be necessary, but can't seem to get my head
 > around how to do it.
 >
 > Any and all advice will be greatly appreciated !

If it will only ever have text for the city OR the country you could do
this:

SELECT if(city <> '', city, country) as citycountry, type
FROM fullist
WHERE type = 'city' or type = 'country'
ORDER BY citycountry;

However, if this is the case (ie you are only storing the city or the
country in the row) then you may be better off to have the data in two
separate tables.

--
Chris Hope - The Electric Toolbox - <a rel="nofollow" style='text-decoration: none;' href="http://www.electrictoolbox.com/" target="_blank">http://www.electrictoolbox.com/</a>
 >> Stay informed about: ORDER BY two columns ? 

usenet DeleteThis @isotopeREEMOOVEmedia.com wrote:
 > I agree with the general idea of what you suggested : I think I need to create
 > a third field in the query and assign it the value of either 'city' or 'state'
 > depeding on the value of 'type.' But apparently that's beyond my skills so far.
 > <g>

I don't believe it's necessary to have another field in your
select-list. You can use expressions in the ORDER BY clause.
Does this do what you want?

SELECT city, county
FROM fullist
WHERE type = 'city' or type = 'county'
ORDER BY IF(type = 'city', city, country);

Regards,
Bill K.
 >> Stay informed about: ORDER BY two columns ? 

Bill Karwin wrote:


  >> I agree with the general idea of what you suggested : I think I need
  >> to create a third field in the query and assign it the value of
  >> either 'city' or 'state'
  >> depeding on the value of 'type.' But apparently that's beyond my
  >> skills so far. <g>
 >
 > I don't believe it's necessary to have another field in your
 > select-list. You can use expressions in the ORDER BY clause.
 > Does this do what you want?
 >
 > SELECT city, county
 > FROM fullist
 > WHERE type = 'city' or type = 'county'
 > ORDER BY IF(type = 'city', city, country);

Nice solution.

--
Chris Hope - The Electric Toolbox - <a rel="nofollow" style='text-decoration: none;' href="http://www.electrictoolbox.com/" target="_blank">http://www.electrictoolbox.com/</a>
 >> Stay informed about: ORDER BY two columns ? 

this post is not archived.
 >> Stay informed about: ORDER BY two columns ?