Re: SQLite - Unwanted values using group-by

On Sun, Jul 8, 2012 at 12:07 AM, Simon Schick <simonsimcity@gmail.com> wrote: > Hi, All > > May you have an idea ... > > Here's the full code-example: > http://viper-7.com/M5mldG > > I have the following SQL command: > > SELECT max(r.month+r.year*100), r.year, r.month > FROM base b LEFT JOIN remote r ON b.id = r.remote_id > GROUP BY r.remote_id > > Now I expect that the first column in the results should look like a > combination of the second and third one .. so f.e. this: > array(3) { ["max(r.month+r.year*100)"]=> string(6) "201201" > ["year"]=> > string(4) "2012" ["month"]=> string(2) "01" } > > But instead I get this result: > array(3) { ["max(r.month+r.year*100)"]=> string(6) "201201" > ["year"]=> > string(4) "2011" ["month"]=> string(2) "12" } 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 - Matijn