On Wed, Jul 11, 2012 at 1:45 AM, Simon Schick <simonsimc...@gmail.com> wrote:
> On Sun, Jul 8, 2012 at 12:33 AM, Matijn Woudt <tijn...@gmail.com> wrote:
>> Both of the results are valid outcomes. I think you don't understand
>> the GROUP BY clause well enough. The parameters in the SELECT clause,
>> should be either
>> 1) an aggregate function (like the max function you're using)
>> 2) one of the parameters in the GROUP BY clause.
>> If not one of the above, it will return 'random' values for r.month
>> and r.year. (probably the first it finds, which might differ in your
>> test cases)
>> - Matijn
> Hi, Matijn
> But I think you get what I mean, when I say that max() should only
> point to one row, in this case the one with the latest date ;)
> How is it possible to, for sure, get the data of this rows?
> Btw: Here's someone talking about that ... Out of reading this, it
> should work as expected.
> http://stackoverflow.com/questions/1752556/is-it-safe-to-include-extra-columns-in-the-select-list-of-a-sqlite-group-by-quer#answers
> Bye
> Simon


Let me quote an important aspect of that answer, so it is in the
mailing list forever:
"You can use these queries "safely," that is, without getting
ambiguous results, if the extra columns are functionally dependent on
the column(s) you group by"

Since you group based on r.remote_id, that would mean that for each
r.remote_id the query should only return 1 row, or multiple rows with
the same result. Your example data does not meet that criteria.

To do this, you probably need something with a subquery, like this:
SELECT r.year, r.month
FROM base b
LEFT JOIN remote r on b.id = r.remote_id
WHERE r.year*100+r.month =
    FROM base b
    LEFT JOIN remote r ON b.id = r.remote_id)
GROUP BY r.remote_id

If it's not too late, I would suggest to use timestamps instead of
month/year field combinations, because that just makes you're life a
lot easier. Also, you could just return the MAX(r.year*100+r.month)
field, and split that one again in the two different fields.

- Matijn

PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to