Hi, Yes, "sample" would be nice. With the new storage engine (MVStore), it should be possible to support it quite easily. It's also good to have for the query optimizer. There is something similar: "sample_size": http://h2database.com/html/grammar.html?highlight=SAMPLE_SIZE&search=sample_siz#select- but it's not (as) random.
Regards, Thomas On Tue, Apr 29, 2014 at 8:04 AM, Lukas Eder <[email protected]> wrote: > 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]> >> 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. > -- 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.
