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

Reply via email to