From: | Christian Ullrich <chris(at)chrullrich(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Group By Question |
Date: | 2010-10-02 14:47:06 |
Message-ID: | i87gl5$hot$1@dough.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
* 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
From | Date | Subject | |
---|---|---|---|
Next Message | Rajesh Kumar Mallah | 2010-10-02 16:02:44 | streaming replication question |
Previous Message | Vincenzo Romano | 2010-10-02 09:40:41 | [PL/PgSQL] error checks vs error trapping |