Skip site navigation (1) Skip section navigation (2)

Re: ORDER BY Clause

From: Derrick Betts <list(at)blueaxis(dot)com>
To: Frank Bax <fbax(at)sympatico(dot)ca>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: ORDER BY Clause
Date: 2008-04-13 18:38:58
Message-ID: 48025342.4060607@blueaxis.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: nickDate: 2008-04-14 10:52:44
Subject: Re: convert text file to database
Previous:From: Shane AmblerDate: 2008-04-13 11:20:36
Subject: Re: convert text file to database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group