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 > <c.dhul...@surveybe.com<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 h2-database+unsubscr...@googlegroups.com. >> To post to this group, send email to h2-database@googlegroups.com. >> 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
