PostgreSQL SQL - consistent random order

You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

Author

consistent random order

Jeff Herrin

2006-11-29, 7:20 pm

I am returning results ordered randomly using 'order by random()'. My issuehas to do with page numbers in our web application. When I hit the 2nd page and retrieve results with an offset, ordering by random() isn't really what I want since I will often receive results that were on the 1st page (they get re-randomized).

I'm looking for a wayÂ_to order in a controled random order. Maybe a UDF. Ideally I would need to do this:

ORDER BY myRandomUDF(1234)
or
ORDER BY myRandomUDF(2345)

Where the argument acts like a seed that always returns a consistent pseudo-random set. That way, when I get to the 2nd page, i know i'm getting the dataset back in the same order that I had on page 1, and the offset works like normal. Is this even realistically possible?

Thanks,
Jeff Herrin

Sponsored Links