Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 30BF89FB276 for ; Wed, 29 Nov 2006 13:24:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 04213-07 for ; Wed, 29 Nov 2006 13:23:42 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.177]) by postgresql.org (Postfix) with ESMTP id 24AC09FB1F8 for ; Wed, 29 Nov 2006 13:24:01 -0400 (AST) Received: by py-out-1112.google.com with SMTP id d32so1501644pye for ; Wed, 29 Nov 2006 09:24:00 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=kce1QHZeHDiUx+kfZzDSjHBT8RIqf5tY2PBoqoGjpsRQgf8BOAHMaBMQBkf0GFYBPu++/cpvL40duM5kyVJnCr3s/JjbyghbEgTZ/iCZuO2h0wU66KQurJCg7IUXWkKEoBUNtkda5tHXkiXHY0j7e31qfTWaZFqO8qOHbMxDgTA= Received: by 10.78.128.15 with SMTP id a15mr2491581hud.1164821039360; Wed, 29 Nov 2006 09:23:59 -0800 (PST) Received: by 10.78.136.6 with HTTP; Wed, 29 Nov 2006 09:23:54 -0800 (PST) Message-ID: Date: Wed, 29 Nov 2006 11:23:54 -0600 From: "Aaron Bono" To: "Jeff Herrin" Subject: Re: consistent random order Cc: pgsql-sql@postgresql.org In-Reply-To: <15697722.58781164818297562.JavaMail.root@srv161.carpathiahost.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_26632_22425567.1164821034035" References: <15697722.58781164818297562.JavaMail.root@srv161.carpathiahost.net> X-Google-Sender-Auth: 569be8f21cbac21e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/274 X-Sequence-Number: 26926 ------=_Part_26632_22425567.1164821034035 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/29/06, Jeff Herrin wrote: > > 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 ================================================================== ------=_Part_26632_22425567.1164821034035 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/29/06, Jeff Herrin <jeff@alternateimage.com> wrote:

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
================================================================== ------=_Part_26632_22425567.1164821034035--