Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 89B88475923 for ; Sun, 18 Aug 2002 08:38:09 -0400 (EDT) Received: from wolff.to (wolff.to [66.92.219.49]) by postgresql.org (Postfix) with SMTP id B497C47583A for ; Sun, 18 Aug 2002 08:38:08 -0400 (EDT) Received: (qmail 7667 invoked by uid 500); 18 Aug 2002 12:45:01 -0000 Date: Sun, 18 Aug 2002 07:45:01 -0500 From: Bruno Wolff III To: Julian Scarfe Cc: pgsql-sql@postgresql.org Subject: Re: Ordering with GROUPs Message-ID: <20020818124501.GB7589@wolff.to> Mail-Followup-To: Julian Scarfe , pgsql-sql@postgresql.org References: <046401c246ad$48dbdc10$0500a8c0@Wilbur> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <046401c246ad$48dbdc10$0500a8c0@Wilbur> User-Agent: Mutt/1.3.25i X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/258 X-Sequence-Number: 9104 On Sun, Aug 18, 2002 at 12:49:18 +0100, Julian Scarfe wrote: > I'm struggling to find an appropriate efficient query for an aggregate-type > problem and I'd appreciate suggestions. > > I have messages associated with a set of locations (zero or more messages > per location). A cut down test is as follows: > > CREATE TABLE location (ident char(4), node point); > INSERT INTO location values ('AAAA', '(1,1)'); > INSERT INTO location values ('BBBB', '(1,2)'); > INSERT INTO location values ('CCCC', '(2,1)'); > INSERT INTO location values ('DDDD', '(2,2)'); > > CREATE TABLE message (ident char(4), message_text text); > INSERT INTO message values ('AAAA', 'foo'); > INSERT INTO message values ('AAAA', 'bar'); > INSERT INTO message values ('AAAA', 'baz'); > INSERT INTO message values ('BBBB', 'abel'); > INSERT INTO message values ('BBBB', 'baker'); > INSERT INTO message values ('CCCC', 'charlie'); > > I can't find anything in the doc. > > One alternative is to use a nested query: > Instead of a nested query, how about doing a join to a group'd version of message? While you didn't use a unique constraint on the location table it seems likely that there should only be one location for each ident. So you can do the join after counting the number of messages. While this is similar to your example, it might run faster, especially in you don't have the message table indexed by ident, so that you can do one sort instead of a bunch of sequential scans. For example: area=> select location.ident, location.node, howmany from location area-> natural join (select ident, count(*) as howmany from message area(> group by ident) as messgroup; ident | node | howmany -------+-------+--------- AAAA | (1,1) | 3 BBBB | (1,2) | 2 CCCC | (2,1) | 1 (3 rows)