On 18/3/62 17:36, Kevin Martin wrote:
Hi,

I am trying to use a correlated subquery with an 'order by random() limit 2' to 
pick upto two random rows for each value in the outer query. I am not sure if I 
am doing this correctly, but the number of rows I am getting seems to vary 
randomly which doesn't make sense to me. If i replace the order by random() 
with order by product I always get the expected number of rows. I have tried to 
create a simplified version of the code below to replicate the issue.
with
   dareas as (select distinct
     area
   from
     test_productarea)
   select
     da.area,
     pa.product
   from
     dareas as da
     left join test_productarea as pa
       --if I don't order by random, but instead by product, I always get the 
correct number of rows
       on pa.product in (select product from test_productarea where 
da.area=area order by random() limit 2)

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.

Dan.




-- In the real query, this order by is required to see a different number of 
rows to the number in the
-- limit. However, it seems it is not required in the small test dataset.
--  order by
--    da.area,
--    pa.product
;
_______________________________________________
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

Reply via email to