Group By Question

["Andrew E. Tegenkamp" <andrew@xxxxxx>]

I have two tables and want to attach and return the most recent data from
the second table.

Table 1 has a counter ID and name. Table 2 has a counter ID, Reference (to
Table 1 ID), Date, and Like. I want to do a query that gets each name and
their most recent like. I have a unique key setup on likes for the reference
and date so I know there is only 1 per day. I can do this query fine:

SELECT test.people.id, test.people.name, test.likes.ref,
MAX(test.likes.date)
FROM test.people LEFT JOIN test.likes ON test.people.id = test.likes.ref
GROUP BY test.people.id, test.people.name, test.likes.ref

However, when I try to add in test.likes.id OR test.likes.likes I get an
error that it has to be included in the Group By (do not want that) or has
to be an aggregate function. I just want the value of those fields from
whatever row it is getting the MAX(date) field.

How can I return those values? 

Thanks,
Andrew

============
VISUALLY
============

people
============
ID    Name
1     Andrew
2     Bob
3     John
==========
likes
==========
ID    Ref    Date           Likes
1     1      2010-09-30     Candy
2     3      2010-09-30     Fruit
3     3      2010-01-01     Nuts

========================
SQL in test schema
========================
CREATE TABLE test.likes (
  id serial NOT NULL,
  ref integer NOT NULL,
  date date,
  likes character varying(255),
  CONSTRAINT like_pkey PRIMARY KEY (id),
  CONSTRAINT likes_ref_key UNIQUE (ref, date)
) WITH (OIDS=FALSE);

CREATE TABLE test.people (
  id serial NOT NULL,
  "name" character varying(255),
  CONSTRAINT people_pkey PRIMARY KEY (id)
) WITH (OIDS=FALSE);

INSERT INTO people (id, name) VALUES (1, 'Andrew');
INSERT INTO people (id, name) VALUES (2, 'Bob');
INSERT INTO people (id, name) VALUES (3, 'John');

INSERT INTO likes (id, ref, date, likes) VALUES (1, 1, '2010-09-30',
'Candy');
INSERT INTO likes (id, ref, date, likes) VALUES (2, 3, '2010-09-30',
'Fruit');
INSERT INTO likes (id, ref, date, likes) VALUES (3, 3, '2010-01-01',
'Nuts');



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general