Thread: consistent random order
<p>I am returning results ordered randomly using 'order by random()'. My issue has 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 sinceI will often receive results that were on the 1st page (they get re-randomized).<br /><br />I'm looking for a way toorder in a controled random order. Maybe a UDF. Ideally I would need to do this:<br /><br />ORDER BY myRandomUDF(1234)<br/>or<br />ORDER BY myRandomUDF(2345)<p>Where the argument acts like a seed that always returns a consistentpseudo-random set. That way, when I get to the 2nd page, i know i'm getting the dataset back in the same orderthat I had on page 1, and the offset works like normal. Is this even realistically possible?<br /><br />Thanks,<br />JeffHerrin
On 11/29/06, Jeff Herrin <jeff@alternateimage.com> wrote:
Why not create a random seed between 1 and the number of possibilities in your web application when a user first hits the site, store that in the session and then increment it by 1 (do a modulus to wrap the numbers back to 1) and just select with an offset equal to this number? That way you get the first item chosen at random but the rest are sequential. If you want this somewhat random, create a "random" ordering field on the table and sort by that so the pages are not sorted by name or id or whatever else it may normally sort by.
You could then take it further and use cookies so the next time that person comes to the site, the "random" page picks up where it left off.
Just an idea...
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
I am returning results ordered randomly using 'order by random()'. My issue has 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?
Why not create a random seed between 1 and the number of possibilities in your web application when a user first hits the site, store that in the session and then increment it by 1 (do a modulus to wrap the numbers back to 1) and just select with an offset equal to this number? That way you get the first item chosen at random but the rest are sequential. If you want this somewhat random, create a "random" ordering field on the table and sort by that so the pages are not sorted by name or id or whatever else it may normally sort by.
You could then take it further and use cookies so the next time that person comes to the site, the "random" page picks up where it left off.
Just an idea...
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
Jeff Herrin <jeff@alternateimage.com> schrieb: > I am returning results ordered randomly using 'order by random()'. My issue has > 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. I think you are searching for CURSORs. 18:25 < akretschmer> ??cursor 18:25 < rtfm_please> For information about cursor 18:25 < rtfm_please> see http://www.postgresql.org/docs/current/static/plpgsql-cursors.html 18:25 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-declare.html With a CURSOR, you get one result-set and can walk through this result. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
I need it a little more random than that. In both these scenarios, the same items will always follow each other. <br /><br/>Jeff<br /><br />----- Original Message -----<br />Why not create a random seed between 1 and the number of possibilitiesin your web application when a user first hits the site, store that in the session and then increment it by1 (do a modulus to wrap the numbers back to 1) and just select with an offset equal to this number? That way you get thefirst item chosen at random but the rest are sequential. If you want this somewhat random, create a "random" orderingfield on the table and sort by that so the pages are not sorted by name or id or whatever else it may normally sortby. <br /><br />You could then take it further and use cookies so the next time that person comes to the site, the "random"page picks up where it left off.<br /><br />Just an idea...<br clear="all" /><br />-- <br />==================================================================<br /> Aaron Bono<br /> Aranya Software Technologies,Inc.<br /> <a href="http://www.aranya.com/" target="_blank">http://www.aranya.com</a><br /> <a href="http://codeelixir.com/"target="_blank">http://codeelixir.com</a><br />==================================================================
I don't think cursors are going to help in this case. The order by random() is still going to give different result setson different pages. Jeff ----- Original Message ----- From: Andreas Kretschmer <akretschmer@spamfence.net> To: pgsql-sql@postgresql.org Sent: Wednesday, November 29, 2006 12:27:42 PM GMT-0500 US/Eastern Subject: Re: [SQL] consistent random order Jeff Herrin <jeff@alternateimage.com> schrieb: > I am returning results ordered randomly using 'order by random()'. My issue has > 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. I think you are searching for CURSORs. 18:25 < akretschmer> ??cursor 18:25 < rtfm_please> For information about cursor 18:25 < rtfm_please> see http://www.postgresql.org/docs/current/static/plpgsql-cursors.html 18:25 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-declare.html With a CURSOR, you get one result-set and can walk through this result. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
On Wed, Nov 29, 2006 at 12:32:56PM -0500, Jeff Herrin wrote: > I don't think cursors are going to help in this case. The order > by random() is still going to give different result sets on different > pages. Have you tried using setseed() to seed the random number generator to the same value before each query? -- Michael Fuhr
Jeff Herrin wrote: > I don't think cursors are going to help in this case. The order by random() is still going to give different result setson different pages. > > Jeff A cursor will maintain the order it was created with until it is disposed of. It won't work with a web app though as each page will come from a different connection in the available pool (or created for each page) meaning you will loose the cursor between pages. I would think you want to look at having a sort column that has a random number in it that is used for sorting. mysortcol integer default random() or maybe update the column a couple of times a day to keep the variety you seem to be after. > ----- Original Message ----- > From: Andreas Kretschmer <akretschmer@spamfence.net> > To: pgsql-sql@postgresql.org > Sent: Wednesday, November 29, 2006 12:27:42 PM GMT-0500 US/Eastern > Subject: Re: [SQL] consistent random order > > Jeff Herrin <jeff@alternateimage.com> schrieb: > >> I am returning results ordered randomly using 'order by random()'. My issue has >> 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. > > I think you are searching for CURSORs. > > 18:25 < akretschmer> ??cursor > 18:25 < rtfm_please> For information about cursor > 18:25 < rtfm_please> see http://www.postgresql.org/docs/current/static/plpgsql-cursors.html > 18:25 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-declare.html > > With a CURSOR, you get one result-set and can walk through this result. > > > Andreas -- Shane Ambler pgSQL@007Marketing.com Get Sheeky @ http://Sheeky.Biz
Michael,
I think you may have solved my problem. We're still experimenting with it but I think setseed is going to work. Thank
youvery, very much!
Jeff Herrin
----- Original Message -----
From: Michael Fuhr <mike@fuhr.org>
To: Jeff Herrin <jeff@alternateimage.com>
Cc: Andreas Kretschmer <akretschmer@spamfence.net>, pgsql-sql@postgresql.org
Sent: Thursday, November 30, 2006 2:58:23 AM GMT-0500 US/Eastern
Subject: Re: [SQL] consistent random order
On Wed, Nov 29, 2006 at 12:32:56PM -0500, Jeff Herrin wrote:
> I don't think cursors are going to help in this case. The order
> by random() is still going to give different result sets on different
> pages.
Have you tried using setseed() to seed the random number generator
to the same value before each query?
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq