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
