On 5/8/08, Barbara Weinberg <[EMAIL PROTECTED]> wrote: > This is my query: > select phn,random() as ran from demographics where sex='F' and dob >= > 19400401 and dob <=19450331 order by ran limit 500 > The original table has 7 million records and I am trying to select a subset > of about 40000 and sort them in random order. I am doing this to get a > random 500 records.
I don't know what the 40000 is doing there, but I would first select all the records, and then shuffle them in memory outside the db. As I showed in my Perl script, it will be way much faster to do so. > > > > On Thu, May 8, 2008 at 9:17 AM, John Stanton <[EMAIL PROTECTED]> wrote: > > > Dennis Cote wrote: > > > Barbara Weinberg wrote: > > >> I was wondering whether anyone had tried sorting records in random > > order > > >> using sqlite3. I tried sorting by random() and randomblob() but it was > > very > > >> slow and chewed up lots of resources. Any suggestions? > > > > > > Can you provide any more details about what you are trying to > > accomplish? > > > > > > There have been several previous discussions about selecting random > > > records from a table. You may want to look for those in the archives. If > > > you really need to sort the entire table then you are probably stuck > > > with the slow operation. If you only need a random subset of a table > > > then you may be able to use some of these ideas to speed things up. > > > > > > HTH > > > Dennis Cote > > > > > > > If you have sequential rowids and know the largest then you can use a PRNG > > and get a random rowid to lookup. That will be optimally fast. > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
