> On 18 Mar 2019, at 16:15, Dan Kennedy <danielk1...@gmail.com> wrote: > > > In SQLite, a correlated sub-query on the RHS of an IN(...) operator may be > rerun every time the IN(...) test is required. And if that sub-query contains > "random()" it might return a different result every time. > > Your words suggest that you are hoping it will be run once for each different > value of "da.area", with different results each time. But it will not.
Ah yes, this makes complete sense now, thanks. I was going off a stack overflow post as to how to emulate outer apply in sqlite3. I didn't really think through what I was writing. In my case, I only need a different ordering each time I create the database, as opposed to each time the query is run. So, I can remove the random() from the subquery, and instead create a new table populated by a trigger mapping product to a random number and order by that in the subquery instead. I would be interested if there is a solution for sqlite 3.22 (i.e. no window functions) where it can be done so that the query gives a possibly different result each time it is executed. Thanks, Kevin _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
