Re: Group By Question

* 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