Ordering with GROUPs - Mailing list pgsql-sql

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');

so each message is associated with a location as follows -- here's the
natural join

SELECT location.ident, node, message_text from location, message
WHERE location.ident = message.ident;ident | node  | message_text
-------+-------+--------------AAAA  | (1,1) | fooAAAA  | (1,1) | barAAAA  | (1,1) | bazBBBB  | (1,2) | abelBBBB  |
(1,2)| bakerCCCC  | (2,1) | charlie
 
(6 rows)

What I want is to know how many messages are available for each location.
It's easy if I just want the ident:

SELECT location.ident, count(*) from location, message
WHERE location.ident = message.ident
GROUP BY location.ident;ident | count
-------+-------AAAA  |     3BBBB  |     2CCCC  |     1
(3 rows)

But I'd like to return the "node" in the same query.  If I try:

SELECT location.ident, node, count(*) from location, message
WHERE location.ident = message.ident
GROUP BY location.ident;
ERROR:  Attribute location.node must be GROUPed or used in an aggregate
function

it obviously fails.  If node were an integer I could just use an aggregate
like max() or similar, but it's not, and there's no suitable aggregate for
point.  I can create a trivial one as a work around, but I hope I don't have
to.

But if I do it properly, it requires an ordering operator :

SELECT location.ident, node, count(*) from location, message
WHERE location.ident = message.ident
GROUP BY location.ident, location.node;
ERROR:  Unable to identify an ordering operator '<' for type 'point'       Use an explicit ordering operator or modify
thequery
 

So how do I specify the operator for GROUP BY?  If I compare with ORDER BY
(>> operator is "is right of" for type point):

SELECT location.ident, node from location, message
WHERE location.ident = message.ident
ORDER BY location.node USING >>, location.ident;ident | node
-------+-------CCCC  | (2,1)AAAA  | (1,1)AAAA  | (1,1)AAAA  | (1,1)BBBB  | (1,2)BBBB  | (1,2)
(6 rows)

...and use the same construction, I get a parser error...

SELECT location.ident, node from location, message
WHERE location.ident = message.ident
GROUP BY location.node USING >>, location.ident;
ERROR:  parser: parse error at or near ","

I can't find anything in the doc.

One alternative is to use a nested query:

SELECT location.ident, node, (      select count(*) from message      WHERE location.ident = message.ident
) as count
FROM location;ident | node  | count
-------+-------+-------AAAA  | (1,1) |     3BBBB  | (1,2) |     2CCCC  | (2,1) |     1DDDD  | (2,2) |     0
(4 rows)

For the test that works fine, but for my real life situation, the nested
query seems to be very inefficient, taking vastly longer than the first
query illustrated above.  Since the information required is clearly
contained in the result of that query, it seems unfortunate to say the least
that I can't work the GROUP BY mechanism on it to give me what I want.

Any tips please?

Thanks

Julian Scarfe