ORDER BY two columns ?

[usenet@isotopeREEMOOVEmedia.com]

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 !

[Chris Hope]

Re: ORDER BY two columns ?

usenet@isotopeR EEMOOVEmedia.co m wrote:
[color=blue]
> 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 ![/color]

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 - http://www.electrictoolbox.com/

[usenet@isotopeREEMOOVEmedia.com]

Re: ORDER BY two columns ?

On Fri, 11 Feb 2005 12:02:10 +1300, Chris Hope <blackhole@elec trictoolbox.com >
wrote:
[color=blue]
>usenet@isotope REEMOOVEmedia.c om wrote:
>[color=green]
>> 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 ![/color]
>
>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.[/color]

Thanks, Chris. In fact there often is data in both city & state fields, so just
testing wheter or not one or the other contains data won't work. Sorry, I
didn't make that clear originally with my 'x's.

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>


Glenn

[usenet@isotopeREEMOOVEmedia.com]

Re: ORDER BY two columns ?

On Fri, 11 Feb 2005 12:02:10 +1300, Chris Hope <blackhole@elec trictoolbox.com >
wrote:
[color=blue]
>usenet@isotope REEMOOVEmedia.c om wrote:
>[color=green]
>> 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 ![/color]
>
>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.[/color]

Thanks, Chris. In fact there often is data in both city & state fields, so just
testing wheter or not one or the other contains data won't work. Sorry, I
didn't make that clear originally with my 'x's.

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>


Glenn

[Bill Karwin]

Re: ORDER BY two columns ?

usenet@isotopeR EEMOOVEmedia.co m wrote:[color=blue]
> 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>[/color]

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.

[Chris Hope]

Re: ORDER BY two columns ?

Bill Karwin wrote:
[color=blue]
> usenet@isotopeR EEMOOVEmedia.co m wrote:[color=green]
>> 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>[/color]
>
> 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);[/color]

Nice solution.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[usenet@isotopeREEMOOVEmedia.com]

Re: ORDER BY two columns ?

On Thu, 10 Feb 2005 15:37:57 -0800, Bill Karwin <bill@karwin.co m> wrote:
[color=blue]
>usenet@isotope REEMOOVEmedia.c om wrote:[color=green]
>> 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>[/color]
>
>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, county);
>
>Regards,
>Bill K.[/color]

Sweet -- and simpler than I thought ! I had no idea expressions could be
employed in the ORDER BY.

Thanks so much.