Обсуждение: 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