473,226 Members | 1,529 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

selecting random rows

Is there a way to get random rows besides ORDER BY random()? The problem with ORDER BY
random() is that is has to get all the rows from the table before the results are returned.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #1
12 16325
Joseph Shraibman wrote:
Is there a way to get random rows besides ORDER BY random()? The problem with ORDER BY
random() is that is has to get all the rows from the table before the results are returned.


Yes, I think one person's idea was to assign a unique value to every
row, then do:

WHERE col > random()
ORDER BY col
LIMIT 1

or something like that.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #2
Joseph Shraibman wrote:
Is there a way to get random rows besides ORDER BY random()? The problem with ORDER BY
random() is that is has to get all the rows from the table before the results are returned.


Yes, I think one person's idea was to assign a unique value to every
row, then do:

WHERE col > random()
ORDER BY col
LIMIT 1

or something like that.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #3
If you have a nice small Primary key on the table, you can so something
like this:

SELECT field_list
FROM table
WHERE primary_key IN(
SELECT primary_key,
FROM table
ORDER by RANDOM()
LIMIT your_limit);

This may not be the exact sequence, and there is some workarounds for
some slowness in the IN() keyword. Others may comment at will, and
polish this up?

Joseph Shraibman wrote:
Is there a way to get random rows besides ORDER BY random()? The
problem with ORDER BY random() is that is has to get all the rows from
the table before the results are returned.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #4
If you have a nice small Primary key on the table, you can so something
like this:

SELECT field_list
FROM table
WHERE primary_key IN(
SELECT primary_key,
FROM table
ORDER by RANDOM()
LIMIT your_limit);

This may not be the exact sequence, and there is some workarounds for
some slowness in the IN() keyword. Others may comment at will, and
polish this up?

Joseph Shraibman wrote:
Is there a way to get random rows besides ORDER BY random()? The
problem with ORDER BY random() is that is has to get all the rows from
the table before the results are returned.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #5
Dennis Gearon wrote:
If you have a nice small Primary key on the table, you can so something
like this:

SELECT field_list
FROM table
WHERE primary_key IN(
SELECT primary_key,
FROM table
ORDER by RANDOM()
LIMIT your_limit);

This may not be the exact sequence, and there is some workarounds for
some slowness in the IN() keyword. Others may comment at will, and
polish this up?

What exactly does that do for me? Postgres still has to go over the whole table to get
the primary keys.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #6
Dennis Gearon wrote:
If you have a nice small Primary key on the table, you can so something
like this:

SELECT field_list
FROM table
WHERE primary_key IN(
SELECT primary_key,
FROM table
ORDER by RANDOM()
LIMIT your_limit);

This may not be the exact sequence, and there is some workarounds for
some slowness in the IN() keyword. Others may comment at will, and
polish this up?

What exactly does that do for me? Postgres still has to go over the whole table to get
the primary keys.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #7
Joseph Shraibman <jk*@selectacast.net> writes:
Is there a way to get random rows besides ORDER BY random()?


Are you willing to expend an extra column in the table, plus an index on
the column, to make this fast? Then you can do it --- see discussion
just a few days ago.
http://archives.postgresql.org/pgsql...8/msg00526.php

If you are willing to settle for "only approximately random", you might
be able to use the primary key as a pseudo-random value. Again, see
prior discussion.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #8
Joseph Shraibman <jk*@selectacast.net> writes:
Is there a way to get random rows besides ORDER BY random()?


Are you willing to expend an extra column in the table, plus an index on
the column, to make this fast? Then you can do it --- see discussion
just a few days ago.
http://archives.postgresql.org/pgsql...8/msg00526.php

If you are willing to settle for "only approximately random", you might
be able to use the primary key as a pseudo-random value. Again, see
prior discussion.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #9
Joseph Shraibman wrote:
Dennis Gearon wrote:
If you have a nice small Primary key on the table, you can so
something like this:

SELECT field_list
FROM table
WHERE primary_key IN(
SELECT primary_key,
FROM table
ORDER by RANDOM()
LIMIT your_limit);

This may not be the exact sequence, and there is some workarounds for
some slowness in the IN() keyword. Others may comment at will, and
polish this up?

What exactly does that do for me? Postgres still has to go over the
whole table to get the primary keys.

But it only caches the keys in the one sub select, NOT the whole row
that you eventually want. I have not idea whether you just want the
primary key, or several fields.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #10
Joseph Shraibman wrote:
Dennis Gearon wrote:
If you have a nice small Primary key on the table, you can so
something like this:

SELECT field_list
FROM table
WHERE primary_key IN(
SELECT primary_key,
FROM table
ORDER by RANDOM()
LIMIT your_limit);

This may not be the exact sequence, and there is some workarounds for
some slowness in the IN() keyword. Others may comment at will, and
polish this up?

What exactly does that do for me? Postgres still has to go over the
whole table to get the primary keys.

But it only caches the keys in the one sub select, NOT the whole row
that you eventually want. I have not idea whether you just want the
primary key, or several fields.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #11
On Thu, 11 Sep 2003, Joseph Shraibman wrote:
Is there a way to get random rows besides ORDER BY random()? The problem with ORDER BY
random() is that is has to get all the rows from the table before the results are returned.


If you have a column that is a sequence of numbers with no holes, and you
already know the row count, you can get fairly fast random choices from it
with:

select * from accounts where aid = (select (floor(random()*10000)));

as long as the column has an index.

explain analyze select * from accounts where aid = (select
(floor(random()*10000)));
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on accounts (cost=0.02..3177.02 rows=501 width=100) (actual
time=9.34..390.30 rows=1 loops=1)
Filter: ((aid)::double precision = $0)
InitPlan
-> Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.06..0.06
rows=1 loops=1)
Total runtime: 390.48 msec

But the fastest way is to generate your random number in whatever code you
program your apps in (i.e. rand(0,rowcount-1) and use that number with
limit and offset or above if you have a sequential column with no holes in
it.

Really, it depends on how much you'll be doing it. If it's to randomly
pick a banner ad for a website, then it's worth the extra effort to have
such a sequence in your table. If it's a once a day kinda thing, then
performance probably isn't quite as big of an issue.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #12
On Thu, 11 Sep 2003, Joseph Shraibman wrote:
Is there a way to get random rows besides ORDER BY random()? The problem with ORDER BY
random() is that is has to get all the rows from the table before the results are returned.


If you have a column that is a sequence of numbers with no holes, and you
already know the row count, you can get fairly fast random choices from it
with:

select * from accounts where aid = (select (floor(random()*10000)));

as long as the column has an index.

explain analyze select * from accounts where aid = (select
(floor(random()*10000)));
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on accounts (cost=0.02..3177.02 rows=501 width=100) (actual
time=9.34..390.30 rows=1 loops=1)
Filter: ((aid)::double precision = $0)
InitPlan
-> Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.06..0.06
rows=1 loops=1)
Total runtime: 390.48 msec

But the fastest way is to generate your random number in whatever code you
program your apps in (i.e. rand(0,rowcount-1) and use that number with
limit and offset or above if you have a sequential column with no holes in
it.

Really, it depends on how much you'll be doing it. If it's to randomly
pick a banner ad for a website, then it's worth the extra effort to have
such a sequence in your table. If it's a once a day kinda thing, then
performance probably isn't quite as big of an issue.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Joe Six-Pack | last post by:
Hi, Im having problems in randomly selecting an element in an array that has not been selected before.. in other words, I have an array of answers to questions, then I want to select 5, with one...
0
by: Joseph Shraibman | last post by:
Is there a way to get random rows besides ORDER BY random()? The problem with ORDER BY random() is that is has to get all the rows from the table before the results are returned. ...
7
by: Jean-Francois.Doyon | last post by:
Hello, I'm trying to retrieve a limited number of random rows, and order them by a column, and am not having any luck with that last part: SELECT * FROM tablename ORDER BY random(), id LIMIT...
1
by: Jay | last post by:
Hi All, My users are complaining about the page refreshing when they are selecting multiple rows in a datagrid. Has anyone tried to manage this using javascript? I tried smartnavigation but that...
1
by: sneha123 | last post by:
There will be some 20 questions and for each question there will be 4 choices.what i want to do is to select multiple answers by clicking the checkbox. i m using asp.net,vb.net pls help me we...
1
by: Bob Loveshade | last post by:
I am looking for an example that shows how to select and highlight multiple rows in a DataGrid. My DataGrid is part of a Web User Control which is contained in an ASPX page. I haven't been...
2
by: Arnau Rebassa | last post by:
Hi everybody, I'm doing the following query: select * from messages order by random() limit 1; in the table messages I have more than 200 messages and a lot of times, the message retrieved...
8
by: Kari Lavikka | last post by:
Hi! I have to select a random row from a table where primary key isn't continuous (some rows have been deleted). Postgres just seems to do something strange with my method. -- -- Use the...
48
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.