Frank Bax wrote:
> Derrick Betts wrote:
>> I have a query: SELECT fruit, group_number, ordering_number FROM
>> fruit_groups ORDER BY group_number, order_number;
>>
>> The results look like:
>> fruit | group_number | ordering_number
>> --------+------------------+-------------------
>> cherry | 1 | 1
>> orange | 1 | 2
>> apple | 2 | 1
>> pear | 2 | 2
>> banana | 3 | 1
>> kiwi | 3 | 2
>>
>>
>> I would like the results to look like:
>> fruit | group_number | ordering_number
>> --------+------------------+-------------------
>> apple | 2 | 1
>> orange | 2 | 2
>> banana | 3 | 1
>> kiwi | 3 | 2
>> cherry | 1 | 1
>> orange | 1 | 2
>>
>> I want the group_number to be grouped together, then sorted by the
>> ordering_number, then listed in the output alphabetically by the fruit
>> name listed first in the group_number (as defined by the
>> ordering_number).
>
>
> What?? In your sample data the fruit that comes "first" alphabetically
> within each group also happens to have ordering_number = 1; so which
> field do you want to sort on?
>
> You will want either:
>
> select fruit_groups.* from fruit_groups
> left join
> (select group_number,min(ordering_number) as order
> from fruit_groups group by group_number) as sort
> on sort.group_number=fruit_groups.group_number
> order by sort.order, group_number, ordering_number;
>
> or
>
> select fruit_groups.* from fruit_groups
> left join
> (select group_number,min(fruit) as order
> from fruit_groups group by group_number) as sort
> on sort.group_number=fruit_groups.group_number
> order by sort.order, group_number, ordering_number;
>
> Only min() changed.
>
That's excellent. I hadn't thought about doing a JOIN on the same table
to get the groups I needed. Thanks for your insights.
Derrick