Thread: ORDER BY Clause
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).
Does anyone know how I might modify my statement to get this ordering?
Thanks,
Derrick
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.
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