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.
How can I return those values?
<snip>
I believe you'll need to use SQL subqueries to force a different order of
operations, such as group/max before join, and so on.
Something like this:
SELECT test.people.id, test.people.name,
filt_likes.ref, filt_likes.date, filt_likes.likes
FROM test.people
LEFT JOIN (
SELECT test.likes.*
FROM test.likes
INNER JOIN (
SELECT ref, MAX(date) AS max_date FROM test.likes GROUP BY ref
) AS filt ON test.likes.ref = filt.ref
AND test_likes.date = filt.max_date
) AS filt_likes ON test.people.id = filt_likes.ref
Try testing that.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
