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