Maybe, support for an actual SAMPLE clause would be appropriate (in the long run)?
- Oracle's SAMPLE clause: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#SQLRF55293 - SQL Server's TABLESAMPLE clause: http://technet.microsoft.com/en-us/library/ms189108(v=sql.105).aspx Cheers, Lukas Am Dienstag, 29. April 2014 07:09:30 UTC+2 schrieb Thomas Mueller: > > 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]<javascript:>> > 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]. >> 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. >> > -- 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.
