Thread: group by and order by question
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
GH wrote: ... >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. SELECT type, url, disp_place FROM links ORDER BY type, disp_place; -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "And they said, Believe on the Lord Jesus Christ, and thou shalt be saved, and thy house." Acts 16:31
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