> 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.

sqlite-users mailing list

Reply via email to