Re: Problems with group by ... order by

John D. Burger wrote:
> I can't figure out why the following doesn't work:
>
>   select
>     (case
>         when count1 < 300 then 'Other'
>         else country1
>         end) as country2,
>     sum(count1) as count2
>     from (select coalesce(country, 'None') as country1, count(*) as count1
>             from userProfiles group by country1) as counts1
>     group by country2
>     order by (country2 = 'Other'), count2 desc
>

Do either of these work for you? Note, completely untested, and just off the top
of my head.


select
    (case
        when count1 < 300 then 'Other'
        else country1
        end) as country2,
    sum(count1) as count2
    from (select coalesce(country, 'None') as country1, count(*) as count1
            from userProfiles group by country1) as counts1
    group by country2
    order by (1 = 'Other'), count2 desc


select
    (case
        when count1 < 300 then null
        else country1
        end) as country2,
    sum(count1) as count2
    from (select coalesce(country, 'None') as country1, count(*) as count1
            from userProfiles group by country1) as counts1
    group by country2
    order by (1 is null), count2 desc


--
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________