Re: Random PostreSQL

joe@jWebMedia.com (Joseph Koenig) writes: > Doug, > Thanks for the extensive reply. The problem that I was referring to is > that I have no idea of knowning how many records will be in the > database. It won't necessarily be a consistent number, or even close. > That's what I meant by saying that oidrand() wouldn't work for what I > needed. I changed the 10 to 1, 2, 3, etc and nothing was giving me a > standard number. I figured that when the database got full populated it > would work out, however, I didn't want to go on that assumption, or > assume that there would always be a certain number of records in the > database, if that makes sense. I don't like relying on guesses for my > applications. Anyhow, thanks a lot for the extensive reply. OK, if you want a random N rows from a table you could possibly try to make a random number generator: CREATE TABLE random (rand int4); CREATE FUNCTION rand() RETURNS float AS ' UPDATE random SET rand = (9301 * rand + 49297) % 233280; SELECT rand::float / 233280 FROM random; ' LANGUAGE 'sql'; Seed the generator: INSERT INTO random VALUES (987234); Then use a query like this get your results in random order: SELECT *, rand() AS randomorder FROM mytable ORDER BY randomorder; And use LIMIT to restrict the result to the first N rows. If that's not fast enough, try writing the random number generator as a C function rather than SQL. -robin -- Robin Vickery............................................... Planet-Three, 3A West Point, Warple Way, London, W3 0RG, UK Email: robin@planet-three.net Phone: +44 (0)794 670 6395