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