Group By Question

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

Responses

Browse pgsql-general by date

  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?