pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrandomized order in select?
7+ messages / 5 participants
[nested] [flat]
* randomized order in select?
@ 2004-03-10 16:48 Enver ALTIN <enver.altin@frontsite.com.tr>
2004-03-10 19:02 ` Re: randomized order in select? Bruno Wolff III <bruno@wolff.to>
2004-03-10 19:06 ` Re: randomized order in select? scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 2 replies; 7+ messages in thread
From: Enver ALTIN @ 2004-03-10 16:48 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have got a simple table like this:
create table tips (
id integer primary key unique,
tiptext text
);
and, I've got a website where I'm willing to show these tips in a random
order. Each visitor will get a randomly selected tip. So for now, I have
to do 2 queries:
select id from tips
collect an ID list, choose one randomly and retrieve it. I wish I could
do something like:
select tiptext from tips order by random limit 1
in PostgreSQL.
--
__________
| |
| | Enver ALTIN (a.k.a. skyblue)
| | Software developer, IT consultant
| FRONT |
|==========| FrontSITE Bilgi Teknolojisi A.Þ.
|_____SITE_| http://www.frontsite.com.tr/
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: randomized order in select?
2004-03-10 16:48 randomized order in select? Enver ALTIN <enver.altin@frontsite.com.tr>
@ 2004-03-10 19:02 ` Bruno Wolff III <bruno@wolff.to>
2004-03-10 23:15 ` Re: randomized order in select? Enver ALTIN <enver.altin@frontsite.com.tr>
2004-03-11 05:31 ` Re: randomized order in select? Iain <iain@mst.co.jp>
2004-03-11 06:40 ` Re: randomized order in select? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 3 replies; 7+ messages in thread
From: Bruno Wolff III @ 2004-03-10 19:02 UTC (permalink / raw)
To: Enver ALTIN <enver.altin@frontsite.com.tr>; +Cc: pgsql-sql
On Wed, Mar 10, 2004 at 18:48:17 +0200,
Enver ALTIN <enver.altin@frontsite.com.tr> wrote:
>
> collect an ID list, choose one randomly and retrieve it. I wish I could
> do something like:
>
> select tiptext from tips order by random limit 1
>
> in PostgreSQL.
You can but it won't be very efficient (for large tables) as it will generate
a random ordering for the whole table, probably do a sort and then return the
first record. The only thing different you need to do is add () after random:
select tiptext from tips order by random() limit 1
If the number of tips isn't very large doing the above is probably best.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: randomized order in select?
2004-03-10 16:48 randomized order in select? Enver ALTIN <enver.altin@frontsite.com.tr>
2004-03-10 19:02 ` Re: randomized order in select? Bruno Wolff III <bruno@wolff.to>
@ 2004-03-10 23:15 ` Enver ALTIN <enver.altin@frontsite.com.tr>
2 siblings, 0 replies; 7+ messages in thread
From: Enver ALTIN @ 2004-03-10 23:15 UTC (permalink / raw)
To: pgsql-sql
On Wed, 2004-03-10 at 13:02 -0600, Bruno Wolff III wrote:
> You can but it won't be very efficient (for large tables) as it will generate
> a random ordering for the whole table, probably do a sort and then return the
> first record. The only thing different you need to do is add () after random:
> select tiptext from tips order by random() limit 1
Thanks. The whole table consists of about 100 rows and I don't think it
will grow by, even twice. So this fits my needs just well.
--
__________
| |
| | Enver ALTIN (a.k.a. skyblue)
| | Software developer, IT consultant
| FRONT |
|==========| FrontSITE Bilgi Teknolojisi A.Þ.
|_____SITE_| http://www.frontsite.com.tr/
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: randomized order in select?
2004-03-10 16:48 randomized order in select? Enver ALTIN <enver.altin@frontsite.com.tr>
2004-03-10 19:02 ` Re: randomized order in select? Bruno Wolff III <bruno@wolff.to>
@ 2004-03-11 05:31 ` Iain <iain@mst.co.jp>
2004-03-11 16:28 ` Re: randomized order in select? scott.marlowe <scott.marlowe@ihs.com>
2 siblings, 1 reply; 7+ messages in thread
From: Iain @ 2004-03-11 05:31 UTC (permalink / raw)
To: Enver ALTIN <enver.altin@frontsite.com.tr>; +Cc: pgsql-sql
If you have a lot of tips, you could create a unique indexed tip number
column. Select the highest tip number using:
select tip_number from tips order by tip_number desc limit 1;
Then generate a random number and select using that tip_number.
Of course, you would have to allow for the possibility of missing tip
numbers, by repeating the random number generation/read sequence until you
find something. Since the tip_number isn't the PK of the table, you can
regenerate the tip numbers to eliminate holes from deletions any time you
like. Just reset the sequence to 1 and update all rows with the
nextval(tipnumber_seq).
Sounds like a lot of work to me though...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: randomized order in select?
2004-03-10 16:48 randomized order in select? Enver ALTIN <enver.altin@frontsite.com.tr>
2004-03-10 19:02 ` Re: randomized order in select? Bruno Wolff III <bruno@wolff.to>
2004-03-11 05:31 ` Re: randomized order in select? Iain <iain@mst.co.jp>
@ 2004-03-11 16:28 ` scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 7+ messages in thread
From: scott.marlowe @ 2004-03-11 16:28 UTC (permalink / raw)
To: Iain <iain@mst.co.jp>; +Cc: Enver ALTIN <enver.altin@frontsite.com.tr>; pgsql-sql
On Thu, 11 Mar 2004, Iain wrote:
> If you have a lot of tips, you could create a unique indexed tip number
> column. Select the highest tip number using:
>
> select tip_number from tips order by tip_number desc limit 1;
>
> Then generate a random number and select using that tip_number.
>
> Of course, you would have to allow for the possibility of missing tip
> numbers, by repeating the random number generation/read sequence until you
> find something. Since the tip_number isn't the PK of the table, you can
> regenerate the tip numbers to eliminate holes from deletions any time you
> like. Just reset the sequence to 1 and update all rows with the
> nextval(tipnumber_seq).
>
> Sounds like a lot of work to me though...
Assuming there are ten rows, you can use this:
select * from table limit 1 offset random()*10;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: randomized order in select?
2004-03-10 16:48 randomized order in select? Enver ALTIN <enver.altin@frontsite.com.tr>
2004-03-10 19:02 ` Re: randomized order in select? Bruno Wolff III <bruno@wolff.to>
@ 2004-03-11 06:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2004-03-11 06:40 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Enver ALTIN <enver.altin@frontsite.com.tr>; pgsql-sql
Bruno Wolff III <bruno@wolff.to> writes:
> Enver ALTIN <enver.altin@frontsite.com.tr> wrote:
>> I wish I could do something like:
>> select tiptext from tips order by random limit 1
>> in PostgreSQL.
> You can but it won't be very efficient (for large tables) as it will generate
> a random ordering for the whole table, probably do a sort and then return the
> first record. The only thing different you need to do is add () after random:
> select tiptext from tips order by random() limit 1
If you do need to do this for a large table, I seem to recall that
we've previously worked out reasonable ways to select a random entry
efficiently using an index. Dig around in the mail list archives for
details.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: randomized order in select?
2004-03-10 16:48 randomized order in select? Enver ALTIN <enver.altin@frontsite.com.tr>
@ 2004-03-10 19:06 ` scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 0 replies; 7+ messages in thread
From: scott.marlowe @ 2004-03-10 19:06 UTC (permalink / raw)
To: Enver ALTIN <enver.altin@frontsite.com.tr>; +Cc: pgsql-sql
On Wed, 10 Mar 2004, Enver ALTIN wrote:
> Hi,
>
> I have got a simple table like this:
>
> create table tips (
> id integer primary key unique,
> tiptext text
> );
>
> and, I've got a website where I'm willing to show these tips in a random
> order. Each visitor will get a randomly selected tip. So for now, I have
> to do 2 queries:
>
> select id from tips
>
> collect an ID list, choose one randomly and retrieve it. I wish I could
> do something like:
>
> select tiptext from tips order by random limit 1
You mean like:
select * from table order by random();
That Works as far back as 7.2, maybe before.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-03-11 16:28 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-10 16:48 randomized order in select? Enver ALTIN <enver.altin@frontsite.com.tr>
2004-03-10 19:02 ` Bruno Wolff III <bruno@wolff.to>
2004-03-10 23:15 ` Enver ALTIN <enver.altin@frontsite.com.tr>
2004-03-11 05:31 ` Iain <iain@mst.co.jp>
2004-03-11 16:28 ` scott.marlowe <scott.marlowe@ihs.com>
2004-03-11 06:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-10 19:06 ` scott.marlowe <scott.marlowe@ihs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox