On Tuesday 06 April 2004 21:25, Ken Geis wrote: > I am trying to find an efficient way to draw a random sample from a > complex query. I also want it to be easy to use within my application. > > So I've defined a view that encapsulates the query. The id in the > "driving" table is exposed, and I run a query like: > > select * from stats_record_view > where id in (select id from driver_stats > order by random() > limit 30000);
How about a join? SELECT s.* FROM stats_record_view s JOIN (SELECT id FROM driver_stats ORDER BY random() LIMIT 30000) AS r ON s.id = r.id; Or, what about a cursor and fetch forward (or back?) a random number of rows before each fetch. That's probably not going to be so random though. Also worth checking the various list archives - this has come up in the past, but some time ago. -- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
