On Mon, Jun 20, 2016 at 5:18 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> > 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. > > saying [some] 'one of' mean it isn't deterministic . As data changes, id's get added, analyzes and optimizations are performed... the results may change with time. If you're collapsing a whole branch of a tree into a single line that has one of the values from somewhere on the branch, you might want to think again about what you're doing. as long as the dataset is exactly the same or is modified in ways that don't break the index lookup ordering you'll be 'stable' I dunno it's not deterministic for all time > > -----Original Message----- > > From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users- > > boun...@mailinglists.sqlite.org] 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 > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
