You have all ill-phrased correlated subquery. Only by putting c.id in the select list and adding c.id to the group go do you have a properly phrased correlated subquery.
However, The projection (join) will be performed first using whatever order and indexes the optimizer decides to use to do that. Whenever it is going to "pop out" a result the subquery will be executed to provide that data. That means that if there are multiple values of c.id to go with a single value of a.id, you will get a result for "one of" those c.id values. Which one is an implementation detail but is entirely deterministic. > -----Original Message----- > From: [email protected] [mailto:sqlite-users- > [email protected]] On Behalf Of Harmen de Jong - CoachR > Group B.V. > Sent: Monday, 20 June, 2016 09:37 > To: General Discussion of SQLite Database > Subject: [sqlite] Order of applying subquery in SELECT statement > > 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 > > Best regards, > Harmen > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
