Re: [sqlite] Efficient random sampling in a large table using builtin functions.

2012-03-08 Thread Black, Michael (IS)
avies [simon.james.dav...@gmail.com] Sent: Thursday, March 08, 2012 8:47 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Efficient random sampling in a large table using builtin functions. On 8 March 2012 14:37, Black, Michael (IS) wrote: > Glad to know thatcould that pos

Re: [sqlite] Efficient random sampling in a large table using builtin functions.

2012-03-08 Thread Simon Davies
lite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Simon Davies [simon.james.dav...@gmail.com] > Sent: Thursday, March 08, 2012 8:33 AM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] Efficient random sampling in a large table using > b

Re: [sqlite] Efficient random sampling in a large table using builtin functions.

2012-03-08 Thread Black, Michael (IS)
] Efficient random sampling in a large table using builtin functions. On 8 March 2012 14:20, Black, Michael (IS) wrote: > You don't say what language you are working in. IN C++ I would just declare > a "set" and put random row numbers in it until I had enough. Then use that &

Re: [sqlite] Efficient random sampling in a large table using builtin functions.

2012-03-08 Thread Simon Davies
On 8 March 2012 14:20, Black, Michael (IS) wrote: > You don't say what language you are working in.  IN C++ I would just declare > a "set" and put random row numbers in it until I had enough.  Then use that > set to build the SQL. > > SQLite's random() doesn't have a seed function so you don't r

Re: [sqlite] Efficient random sampling in a large table using builtin functions.

2012-03-08 Thread Black, Michael (IS)
2 4:10 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] Efficient random sampling in a large table using builtin functions. I have a table with millions of records. When I run a query with ORDER BY random() LIMIT N; the RANDOM() function is evaluated against all rows of my r

[sqlite] Efficient random sampling in a large table using builtin functions.

2012-03-08 Thread Benoit Mortgat
I have a table with millions of records. When I run a query with ORDER BY random() LIMIT N; the RANDOM() function is evaluated against all rows of my result set, then sorting occurs, and as a result the query is slow. In this case the query could be rewritten as: * Generate N, random, row