* Andrew E. Tegenkamp wrote:
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.
SELECT p.name, l.date, l.likes
FROM people p
LEFT JOIN (SELECT l1.ref, l1.date, l1.likes
FROM likes l1
GROUP BY l1.ref, l1.date, l1.likes
HAVING l1.date = (SELECT max(date)
FROM likes
WHERE ref = l1.ref)) l
ON (p.id = l.ref);
Or the newfangled way, replacing the inner subselect with a window:
SELECT p.id, p.name, l.likes
FROM people p
LEFT JOIN (SELECT l1.ref, l1.likes, l1.date, max(l1.date) OVER
(PARTITION BY ref) AS maxdate
FROM likes l1) l
ON (p.id = l.ref AND l.date = l.maxdate);
On this "dataset", the windowed version is estimated to be ~ 60% faster
than the grouped one, and the actual execution time is ~ 20% lower.
--
Christian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
