Re: group by and order by question

GH wrote:

> Here is the table I am using:
> db=> \d links
> Table "links"
> Attribute | Type | Modifier
> ------------+---------+------------------------------------------------
> id | integer | not null default nextval('links_id_seq'::text)
> url | text |
> link_text | text |
> type | integer |
> disp_place | integer | default nextval('links_place_seq'::text)
> Indices: links_disp_place_key,
> links_id_key
>
> 'type' references an id column in a table of link types.
> I need to group rows by the type but order them within each group
> by 'disp_place'. (See result example below.)
>
> Will I have to use seperate queries for each "type"?
> foreach (select distinct type from links order by type)
> select url, link_text from links where type='<type>' order by disp_place
>
> The issue is that rows with type=2 may have values of disp_place that are
> between rows with type=1.
>
> So the table would look like:
> id| url |link_text | type |disp_place
> --+---------+----------+------+----------
> 1 | myurl | My URL | 1 | 1
> 2 | yoururl | Your URL | 2 | 3
> 3 | hisurl | His URL | 1 | 4
>
> I would need a result like
> type | url | disp_place
> -----+---------+----------
> 1 | myurl | 1
> 1 | hisurl | 4
> 2 | yoururl | 3
> etc.
As far as I can tell, you do not want to *group* your data (grouping
implies that you want to use a summary function, such as count(), sum(),
mean() or other functions reporting a characteristic of the group, not
of individual data). You seem to want to *sort* (= order, in SQL
parlance) your data by two hierachically arranged keys.

I *think* that what you aim at is given by :

select type, url, disp_place from links order by type,disp_place;

> Thank you all for you help.
> Pointers to documentation, etc. are welcomed of course.

You should peruse a good database/SQL primer. I have good things to say
about Bruce Momjian's book, available from Addison-Wesley or readable
online on Postgresql's site at :
http://www.postgresql.org/docs/aw_pgsql_book/index.html

Hope this helps !

Emmanuel Charpentier