Hi,
I think the problem is that the sub-query is re-executed for each row of
the outer table, because "rand" is not deterministic. Test case:
drop table test;
create table test(id int, name varchar);
insert into test select x, 'hello ' || x from system_range(1, 10);
select * from test where id=(select top 1 id from test order by rand());
Sometimes it returns no row, sometimes one, sometimes multiple. What works
for me is:
set @x = select top 1 id from test order by rand();
select * from test where id=@x;
But please note the first statement is not very efficient, as it reads all
rows.
Regards,
Thomas
On Monday, April 28, 2014, Chandra DhulipalaV <[email protected]>
wrote:
> Hi
>
> I am trying to retrieve a randomly selected line from a table.
>
> The following statements work fine in 2 lines. The 'id' is just any int
> column in the table.
> set @random = select top 1 id from Table order by rand();
> select * from Table where id=@random;
>
> But if Icombine them into a single statement, it does not work. The inner
> query does not seem to work as expected.
>
> select * from Table where id=(select top 1 id from Table order by rand());
>
> Could anyone please put some light on it?
>
> Thanks
> Chandra
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to
> [email protected]<javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');>
> .
> To post to this group, send email to
> [email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');>
> .
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
