group by and order by question

From: GH <grasshacker(at)over-yonder(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: group by and order by question
Date: 2000-12-24 18:53:04
Message-ID: 20001224125304.A29047@over-yonder.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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

dan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Frank Bax 2000-12-24 21:00:14 Re: Changing Datatypes
Previous Message Aaron Rouse 2000-12-24 05:38:25 Changing Datatypes