Re: ORDER BY Clause

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.