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.
>
> 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