Re: Random query

If you have cursors available, you can do a select count(*), then generate a random number between 1 and that count, and then do a "fetch absolute" to fetch just that one random record. Francois Merle wrote: > > Hi there: > > I have a table that contains only 1 column. A list of URLs. > > I'd like to get a random URL that is not the one passed as argument. > > Is it possible to do that with SQL instead of retrieving all the records > and writing a program that picks one record randomly? > > Francois -- Colin

Colin McGrath wrote: > > If you have cursors available, you can do a select count(*), then generate > a random number between 1 and that count, and then do a "fetch absolute" > to fetch just that one random record. To clarify this would require a SCROLL CURSOR which will perform the query by fetching the ENTIRE table into a temp table then the FETCH ABS will position within that temp table. I suppose you could also use the FIRST N clause to limit the query to the number of rows specified by the random number generator and FETCH them all to get to the last one or use a SCROLL CURSOR again and just FETCH LAST. Either way this is a pretty expensive way to get a single row. I don't count these as practical solutions so I'll stick to me "No" answer. Art S. Kagel > Francois Merle wrote: > > > > Hi there: > > > > I have a table that contains only 1 column. A list of URLs. > > > > I'd like to get a random URL that is not the one passed as argument. > > > > Is it possible to do that with SQL instead of retrieving all the records > > and writing a program that picks one record randomly? > > > > Francois > > -- > Colin