Group By Question

Поиск
Список
Период
Сортировка
От Andrew E. Tegenkamp
Тема Group By Question
Дата
Msg-id 001d01cb617e$3b443350$b1cc99f0$@com
обсуждение исходный текст
Ответы Re: Group By Question  (Darren Duncan <darren@darrenduncan.net>)
Re: Group By Question  (Chris Velevitch <chris.velevitch@gmail.com>)
Список 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');



В списке pgsql-general по дате отправления:

Предыдущее
От: novnovice
Дата:
Сообщение: Re: Merge replication with Postgresql on Windows?
Следующее
От: Michal Politowski
Дата:
Сообщение: Why would a scan take so long?