On 6/20/2016 11:37 AM, Harmen de Jong - CoachR Group B.V. wrote:
I have a query with a subquery (using an aggregate function) in the SELECT statement. Will this subquery be executed before or after the GROUP BY? For example: SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as maxorderno FROM a INNER JOIN c on c.a_id=a.id GROUP BY a.id
Effectively, after: the projection in SELECT clause applies to the whole group. If there are multiple rows in c with the same c.a_id, then one of them will be picked in an unpredictable fashion to run the subuqery. You won't, in general, get max(b.orderno) across the whole group, only across some random item in it.
If you want max(orderno) across the group, just join with b: SELECT a.id, max(b.orderno) as maxorderno FROM a INNER JOIN c on c.a_id=a.id JOIN b ON b.c_id=c.id GROUP BY a.id -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
