From jeff@alternateimage.com Wed Nov  29 17:10:38 2006
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 12A919FB269
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 29 Nov 2006 13:10:38 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 43022-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 29 Nov 2006 13:10:24 -0400 (AST)
X-Greylist: delayed 00:26:10.213063 by SQLgrey-1.7.4
Received: from ai.4ph.com (ai.4ph.com [66.117.56.228])
	by postgresql.org (Postfix) with ESMTP id 322AE9FB26E
	for <pgsql-sql@postgresql.org>; Wed, 29 Nov 2006 13:10:30 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by ai.4ph.com (Postfix) with ESMTP id 5AFAA2308048
	for <pgsql-sql@postgresql.org>; Wed, 29 Nov 2006 11:38:20 -0500 (EST)
X-Virus-Scanned: amavisd-new at 
Received: from ai.4ph.com ([127.0.0.1])
	by localhost (ai.4ph.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id n3hGsr-aDEdT for <pgsql-sql@postgresql.org>;
	Wed, 29 Nov 2006 11:38:18 -0500 (EST)
Received: from ai.4ph.com (ai.4ph.com [66.117.56.228])
	by ai.4ph.com (Postfix) with ESMTP id 8F4062308026
	for <pgsql-sql@postgresql.org>; Wed, 29 Nov 2006 11:38:18 -0500 (EST)
Message-ID: 
 <15697722.58781164818297562.JavaMail.root@srv161.carpathiahost.net>
Date: Wed, 29 Nov 2006 11:38:17 -0500 (EST)
From: Jeff Herrin <jeff@alternateimage.com>
To: pgsql-sql@postgresql.org
Subject: consistent random order
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_1479_27837847.1164818297509"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.611 tagged_above=0 required=5 tests=BAYES_40,
	HTML_00_10, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200611/273
X-Sequence-Number: 26925

------=_Part_1479_27837847.1164818297509
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable



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 pag=
e and retrieve results with an offset, ordering by random() isn't really wh=
at I want since I will often receive results that were on the 1st page (the=
y get re-randomized).=20

I'm looking for a way=C2=A0to order in a controled random order. Maybe a UD=
F. Ideally I would need to do this:=20

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

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 d=
ataset back in the same order that I had on page 1, and the offset works li=
ke normal. Is this even realistically possible?=20

Thanks,=20
Jeff Herrin
------=_Part_1479_27837847.1164818297509
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<html><body><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 since I will often receive results that were on the 1st page (they get re-randomized).<BR><BR>I'm looking for a way&nbsp;to order 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>
<P>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?<BR><BR>Thanks,<BR>Jeff Herrin</P></body></html>
------=_Part_1479_27837847.1164818297509--




From postgresql@aranya.com Wed Nov  29 17:24:10 2006
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 30BF89FB276
	for <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql@postgresql.org>; Wed, 29 Nov 2006 13:24:01 -0400 (AST)
Received: by py-out-1112.google.com with SMTP id d32so1501644pye
	for <pgsql-sql@postgresql.org>; 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: <bf05e51c0611290923s2cfcd332l5ab20e74a606c858@mail.gmail.com>
Date: Wed, 29 Nov 2006 11:23:54 -0600
From: "Aaron Bono" <postgresql@aranya.com>
To: "Jeff Herrin" <jeff@alternateimage.com>
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 <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
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 11/29/06, <b class="gmail_sendername">Jeff Herrin</b> &lt;<a href="mailto:jeff@alternateimage.com">jeff@alternateimage.com</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div><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 since I will often receive results that were on the 1st page (they get re-randomized).
<br><br>I'm looking for a way&nbsp;to order 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>
<p>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?
</p></div>
</blockquote></div><br>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?&nbsp; That way you get the first item chosen at random but the rest are sequential.&nbsp; If you want this somewhat random, create a &quot;random&quot; 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.
<br><br>You could then take it further and use cookies so the next time that person comes to the site, the &quot;random&quot; page picks up where it left off.<br><br>Just an idea...<br clear="all"><br>-- <br>==================================================================
<br>&nbsp;&nbsp; Aaron Bono<br>&nbsp;&nbsp; Aranya Software Technologies, Inc.<br>&nbsp;&nbsp; <a href="http://www.aranya.com">http://www.aranya.com</a><br>&nbsp;&nbsp; <a href="http://codeelixir.com">http://codeelixir.com</a><br>==================================================================

------=_Part_26632_22425567.1164821034035--



From akretschmer@spamfence.net Wed Nov  29 17:27:27 2006
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id A402D9FA1A1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 29 Nov 2006 13:27:27 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 44846-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 29 Nov 2006 13:27:16 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from 137086.vserver.de (vs137086.vserver.de [62.75.137.86])
	by postgresql.org (Postfix) with ESMTP id 9AC7A9FA103
	for <pgsql-sql@postgresql.org>; Wed, 29 Nov 2006 13:27:19 -0400 (AST)
Received: from a-kretschmer.de (p54B38F6B.dip0.t-ipconnect.de
 [84.179.143.107])
	(authenticated bits=0)
	by 137086.vserver.de (8.12.8/8.12.8) with ESMTP id kATHQr6K004095
	for <pgsql-sql@postgresql.org>; Wed, 29 Nov 2006 18:26:59 +0100
Received: from kretschmer by a-kretschmer.de with local (Exim 4.61)
	(envelope-from <andreas@a-kretschmer.de>) id 1GpTDi-00057r-4j
	for pgsql-sql@postgresql.org; Wed, 29 Nov 2006 18:27:42 +0100
Date: Wed, 29 Nov 2006 18:27:42 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: consistent random order
Message-ID: <20061129172742.GA9005@KanotixBox>
References: 
 <15697722.58781164818297562.JavaMail.root@srv161.carpathiahost.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <15697722.58781164818297562.JavaMail.root@srv161.carpathiahost.net>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-GPG-Fingerprint: EE16 3C01 7B9C 10F7 2C8B  3B86 4DB3 D9EE 7F45 84DA
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
X-Lugdd: Gerd Kube
X-Info: My name is root. Just root. And I am licensed to kill -9
User-Agent: mutt-ng 1.5.9i (Linux)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/276
X-Sequence-Number: 26928

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°



From jeff@alternateimage.com Wed Nov  29 17:37:49 2006
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 9CDC29FA1A1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 29 Nov 2006 13:37:49 -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 07395-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 29 Nov 2006 13:37:07 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from ai.4ph.com (ai.4ph.com [66.117.56.228])
	by postgresql.org (Postfix) with ESMTP id D4AED9FA103
	for <pgsql-sql@postgresql.org>; Wed, 29 Nov 2006 13:37:25 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by ai.4ph.com (Postfix) with ESMTP id 39128230805F;
	Wed, 29 Nov 2006 12:31:25 -0500 (EST)
X-Virus-Scanned: amavisd-new at 
Received: from ai.4ph.com ([127.0.0.1])
	by localhost (ai.4ph.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id qPyUft4TzZFI; Wed, 29 Nov 2006 12:31:18 -0500 (EST)
Received: from ai.4ph.com (ai.4ph.com [66.117.56.228])
	by ai.4ph.com (Postfix) with ESMTP id 17A2A2308055;
	Wed, 29 Nov 2006 12:30:58 -0500 (EST)
Message-ID: <6801364.59051164821457152.JavaMail.root@srv161.carpathiahost.net>
Date: Wed, 29 Nov 2006 12:30:57 -0500 (EST)
From: Jeff Herrin <jeff@alternateimage.com>
To: Aaron Bono <postgresql@aranya.com>
Subject: Re: consistent random order
Cc: pgsql-sql@postgresql.org
In-Reply-To: <bf05e51c0611290923s2cfcd332l5ab20e74a606c858@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_1486_2485406.1164821457151"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.473 tagged_above=0 required=5 tests=AWL, BAYES_05,
	HTML_10_20, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200611/277
X-Sequence-Number: 26929

------=_Part_1486_2485406.1164821457151
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

I need it a little more random than that. In both these scenarios, the same=
 items will always follow each other.=20

Jeff=20

----- Original Message -----=20
Why not create a random seed between 1 and the number of possibilities in y=
our web application when a user first hits the site, store that in the sess=
ion 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?=C2=A0 That way you ge=
t the first item chosen at random but the rest are sequential.=C2=A0 If you=
 want this somewhat random, create a "random" ordering field on the table a=
nd sort by that so the pages are not sorted by name or id or whatever else =
it may normally sort by.=20

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.=20

Just an idea...=20

--=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
=C2=A0=C2=A0 Aaron Bono=20
=C2=A0=C2=A0 Aranya Software Technologies, Inc.=20
=C2=A0=C2=A0 http://www.aranya.com=20
=C2=A0=C2=A0 http://codeelixir.com=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
------=_Part_1486_2485406.1164821457151
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><body>I need it a little more random than that. In both these scenari=
os, 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 n=
umber of possibilities in your web application when a user first hits the s=
ite, 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 nu=
mber?&nbsp; That way you get the first item chosen at random but the rest a=
re sequential.&nbsp; If you want this somewhat random, create a "random" or=
dering field on the table and sort by that so the pages are not sorted by n=
ame or id or whatever else it may normally sort by. <BR><BR>You could then =
take it further and use cookies so the next time that person comes to the s=
ite, the "random" page picks up where it left off.<BR><BR>Just an idea...<B=
R clear=3Dall><BR>-- <BR>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
 <BR>&nbsp;&nbsp; Aaron Bono<BR>&nbsp;&nbsp; Aranya Software Technologies, =
Inc.<BR>&nbsp;&nbsp; <A href=3D"http://www.aranya.com/" target=3D_blank>htt=
p://www.aranya.com</A><BR>&nbsp;&nbsp; <A href=3D"http://codeelixir.com/" t=
arget=3D_blank>http://codeelixir.com</A><BR>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D </body></html>
------=_Part_1486_2485406.1164821457151--




From jeff@alternateimage.com Wed Nov  29 17:39:13 2006
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 1BDF29FB21D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 29 Nov 2006 13:39:13 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 59016-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 29 Nov 2006 13:39:05 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from ai.4ph.com (ai.4ph.com [66.117.56.228])
	by postgresql.org (Postfix) with ESMTP id 6734D9FA103
	for <pgsql-sql@postgresql.org>; Wed, 29 Nov 2006 13:39:06 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by ai.4ph.com (Postfix) with ESMTP id DB885230805F;
	Wed, 29 Nov 2006 12:33:06 -0500 (EST)
X-Virus-Scanned: amavisd-new at 
Received: from ai.4ph.com ([127.0.0.1])
	by localhost (ai.4ph.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id LrT-5erxBE-F; Wed, 29 Nov 2006 12:32:58 -0500 (EST)
Received: from ai.4ph.com (ai.4ph.com [66.117.56.228])
	by ai.4ph.com (Postfix) with ESMTP id B05FA230801C;
	Wed, 29 Nov 2006 12:32:57 -0500 (EST)
Message-ID: 
 <25133466.59111164821576601.JavaMail.root@srv161.carpathiahost.net>
Date: Wed, 29 Nov 2006 12:32:56 -0500 (EST)
From: Jeff Herrin <jeff@alternateimage.com>
To: Andreas Kretschmer <akretschmer@spamfence.net>
Subject: Re: consistent random order
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20061129172742.GA9005@KanotixBox>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/278
X-Sequence-Number: 26930

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.

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 iss=
ue has
> to do with page numbers in our web application. When I hit the 2nd page a=
nd
> 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).
>=20
> 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/plpg=
sql-cursors.html
18:25 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-d=
eclare.html

With a CURSOR, you get one result-set and can walk through this result.


Andreas
--=20
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=C2=B0, E 13.5688=
9=C2=B0

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate




From mike@fuhr.org Thu Nov  30 07:58:45 2006
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 6729A9FB1DD
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 30 Nov 2006 03:58:45 -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 57926-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 30 Nov 2006 03:58:38 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by postgresql.org (Postfix) with ESMTP id 8EF179FA8EF
	for <pgsql-sql@postgresql.org>; Thu, 30 Nov 2006 03:58:38 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id kAU7wOFB011382
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 30 Nov 2006 00:58:26 -0700 (MST)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.8/8.13.8) with ESMTP id kAU7wNWC085543;
	Thu, 30 Nov 2006 00:58:23 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.8/8.13.8/Submit) id kAU7wNmZ085542;
	Thu, 30 Nov 2006 00:58:23 -0700 (MST) (envelope-from mfuhr)
Date: Thu, 30 Nov 2006 00:58:23 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Jeff Herrin <jeff@alternateimage.com>
Cc: Andreas Kretschmer <akretschmer@spamfence.net>, pgsql-sql@postgresql.org
Subject: Re: consistent random order
Message-ID: <20061130075822.GA85484@winnie.fuhr.org>
References: <20061129172742.GA9005@KanotixBox>
	<25133466.59111164821576601.JavaMail.root@srv161.carpathiahost.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <25133466.59111164821576601.JavaMail.root@srv161.carpathiahost.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/286
X-Sequence-Number: 26938

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



From pgsql@007Marketing.com Thu Nov  30 10:04:54 2006
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 8F1139F9ECF
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 30 Nov 2006 06:04:54 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 54936-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 30 Nov 2006 06:04:41 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from ipmail02.adl2.internode.on.net (ipmail02.adl2.internode.on.net
	[203.16.214.141])
	by postgresql.org (Postfix) with ESMTP id 845059F946B
	for <pgsql-sql@postgresql.org>; Thu, 30 Nov 2006 06:04:43 -0400 (AST)
Received: from ppp247-71.static.internode.on.net (HELO [192.168.8.200])
	([203.122.247.71])
	by ipmail02.adl2.internode.on.net with ESMTP; 30 Nov 2006 20:34:40 +1030
X-IronPort-AV: i="4.09,478,1157293800";
	d="scan'208"; a="53608260:sNHT40038005"
Message-ID: <456EACB6.4020702@007Marketing.com>
Date: Thu, 30 Nov 2006 20:34:38 +1030
From: Shane Ambler <pgsql@007Marketing.com>
User-Agent: Thunderbird 1.5.0.7 (Macintosh/20060909)
MIME-Version: 1.0
To: Jeff Herrin <jeff@alternateimage.com>
CC: Andreas Kretschmer <akretschmer@spamfence.net>, pgsql-sql@postgresql.org
Subject: Re: consistent random order
References: 
 <25133466.59111164821576601.JavaMail.root@srv161.carpathiahost.net>
In-Reply-To: 
 <25133466.59111164821576601.JavaMail.root@srv161.carpathiahost.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/288
X-Sequence-Number: 26940

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.
> 
> 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



From jeff@alternateimage.com Thu Nov  30 16:08:42 2006
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E2A9E9FA18B
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 30 Nov 2006 12:08:42 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 45982-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 30 Nov 2006 12:08:34 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from ai.4ph.com (ai.4ph.com [66.117.56.228])
	by postgresql.org (Postfix) with ESMTP id ED8449F9ECF
	for <pgsql-sql@postgresql.org>; Thu, 30 Nov 2006 12:08:33 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by ai.4ph.com (Postfix) with ESMTP id EE547230805E;
	Thu, 30 Nov 2006 11:02:23 -0500 (EST)
X-Virus-Scanned: amavisd-new at 
Received: from ai.4ph.com ([127.0.0.1])
	by localhost (ai.4ph.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id OgOccT5fL4Yd; Thu, 30 Nov 2006 11:02:21 -0500 (EST)
Received: from ai.4ph.com (ai.4ph.com [66.117.56.228])
	by ai.4ph.com (Postfix) with ESMTP id CDC36230801F;
	Thu, 30 Nov 2006 11:02:21 -0500 (EST)
Message-ID: 
 <17835952.62811164902540562.JavaMail.root@srv161.carpathiahost.net>
Date: Thu, 30 Nov 2006 11:02:20 -0500 (EST)
From: Jeff Herrin <jeff@alternateimage.com>
To: Michael Fuhr <mike@fuhr.org>
Subject: Re: consistent random order
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20061130075822.GA85484@winnie.fuhr.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/295
X-Sequence-Number: 26947

Michael,

I think you may have solved my problem. We're still experimenting with it but I think setseed is going to work. Thank you very, 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