On Wed, Jul 11, 2012 at 1:45 AM, Simon Schick <simonsimcity@xxxxxxxxx> wrote: > On Sun, Jul 8, 2012 at 12:33 AM, Matijn Woudt <tijnema@xxxxxxxxx> 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 Hi, 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 = (SELECT DISTINCT MAX(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
