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

Re: ORDER BY Clause

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: ORDER BY Clause
Date: 2008-04-13 04:06:01
Message-ID: 480186A9.6070407@sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

Responses

pgsql-novice by date

Next:From: Frank BaxDate: 2008-04-13 04:25:16
Subject: Re: problem with 8.1: createdb: could not connect to database postgres: could not connect to server: No such file or directory
Previous:From: Derrick BettsDate: 2008-04-13 02:52:38
Subject: ORDER BY Clause

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