Re: a ramdom sort select (again)
From: Daniel Esquerre Fernandez <desquerre@sil.edu.pe> > > Anyone know how make a select of many rows y random order, i e some >like select * from mytable order by random What version? I guess you could probably do this with 9.x ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
You can implement this in 4GL , ESQLC etc by using prepared statements. Vivek Chaudhary On Wed, 26 Apr 2000 18:54:35 BST, "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > >From: Daniel Esquerre Fernandez <desquerre@sil.edu.pe> >> >> Anyone know how make a select of many rows y random order, i e some >>like select * from mytable order by random > >What version? I guess you could probably do this with 9.x >________________________________________________________________________ >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com >
pgcs@pgcs.com wrote: > > You can implement this in 4GL , ESQLC etc by using prepared > statements. Vivek is correct. To be specific you need to open a SCROLL cursor and FETCH ABSOLUTE n where n is a random number from 1 to the number of rows returned by the OPEN in the sqlca structure. Art S. Kagel > Vivek Chaudhary > > On Wed, 26 Apr 2000 18:54:35 BST, "Obnoxio The Clown" > <obnoxio@hotmail.com> wrote: > > > > >From: Daniel Esquerre Fernandez <desquerre@sil.edu.pe> > >> > >> Anyone know how make a select of many rows y random order, i e some > >>like select * from mytable order by random > > > >What version? I guess you could probably do this with 9.x > >________________________________________________________________________ > >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com > >
My reply via the informix-list@iiug.org bounced, (is it down?), so I'll try again: Art, even if the random numbers were duplicated as the temp table was being created, ordering by those random numbers during the subsequent select from the temp table won't select the same record twice. It's just that two (or more) records will have the same value in the column being ordered by, so you won't know which will come first, but this is a random sort anyway! And, if you used a big enough range to start with, duplicates would be unlikely, while with a random fetch via scroll cursor, you definitely have to have the range of the random numbers equal to the number of records in the set, so getting the same random number twice would be much more likely. In article <39119A0A.75BF6BB7@bloomberg.net>, kagel@bloomberg.net wrote: > pgcs@pgcs.com wrote: > > > > You can implement this in 4GL , ESQLC etc by using prepared > > statements. > > Vivek is correct. To be specific you need to open a SCROLL cursor and > FETCH ABSOLUTE n where n is a random number from 1 to the number of rows > returned by the OPEN in the sqlca structure. > > Art S. Kagel > > > Vivek Chaudhary > > > > On Wed, 26 Apr 2000 18:54:35 BST, "Obnoxio The Clown" > > <obnoxio@hotmail.com> wrote: > > > > > > > >From: Daniel Esquerre Fernandez <desquerre@sil.edu.pe> > > >> > > >> Anyone know how make a select of many rows y random order, i e some > > >>like select * from mytable order by random > > > > > >What version? I guess you could probably do this with 9.x > > >_____________________________________________________________________ Sent via Deja.com http://www.deja.com/ Before you buy.
mccolingrath@my-deja.com wrote: > > My reply via the informix-list@iiug.org bounced, (is it down?), so I'll > try again: > Art, even if the random numbers were duplicated as the temp table was > being created, ordering by those random numbers during the subsequent > select from the temp table won't select the same record twice. It's > just that two (or more) records will have the same value in the column > being ordered by, so you won't know which will come first, but this is > a random sort anyway! > And, if you used a big enough range to start with, duplicates would be > unlikely, while with a random fetch via scroll cursor, you definitely > have to have the range of the random numbers equal to the number of > records in the set, so getting the same random number twice would be > much more likely. OK, I just do not think that this is a HUGE issue, you are generating the random numbers in your application code so it will just take a few dozen (perhaps) extra function calls to rand(), or whatever, a cost of a few msecs. Art S. Kagel > In article <39119A0A.75BF6BB7@bloomberg.net>, > kagel@bloomberg.net wrote: > > pgcs@pgcs.com wrote: > > > > > > You can implement this in 4GL , ESQLC etc by using prepared > > > statements. > > > > Vivek is correct. To be specific you need to open a SCROLL cursor and > > FETCH ABSOLUTE n where n is a random number from 1 to the number of > rows > > returned by the OPEN in the sqlca structure. > > > > Art S. Kagel > > > > > Vivek Chaudhary > > > > > > On Wed, 26 Apr 2000 18:54:35 BST, "Obnoxio The Clown" > > > <obnoxio@hotmail.com> wrote: > > > > > > > > > > >From: Daniel Esquerre Fernandez <desquerre@sil.edu.pe> > > > >> > > > >> Anyone know how make a select of many rows y random order, i > e some > > > >>like select * from mytable order by random > > > > > > > >What version? I guess you could probably do this with 9.x > > > > >_____________________________________________________________________ > > Sent via Deja.com http://www.deja.com/ > Before you buy.