From: | "Andrew E(dot) Tegenkamp" <andrew(at)g3(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Group By Question |
Date: | 2010-10-01 15:35:09 |
Message-ID: | 001d01cb617e$3b443350$b1cc99f0$@com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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');
From | Date | Subject | |
---|---|---|---|
Next Message | novnovice | 2010-10-01 15:40:43 | Re: Merge replication with Postgresql on Windows? |
Previous Message | Greg Smith | 2010-10-01 15:31:15 | Re: Merge replication with Postgresql on Windows? |