By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,483 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,483 IT Pros & Developers. It's quick & easy.

Random via Subselect or Temporary Table

P: n/a
I would like to select a random record from a group of records so I'd end
up with one random record per group:

CREATE TABLE randtest (
catnum int,
title varchar(32)
);

INSERT INTO randtest VALUES (1, 'one.one');
INSERT INTO randtest VALUES (1, 'one.two');
INSERT INTO randtest VALUES (1, 'one.three');
INSERT INTO randtest VALUES (1, 'one.four');
INSERT INTO randtest VALUES (1, 'one.five');
INSERT INTO randtest VALUES (2, 'two.one');
INSERT INTO randtest VALUES (2, 'two.two');
INSERT INTO randtest VALUES (2, 'two.three');
INSERT INTO randtest VALUES (2, 'two.four');
INSERT INTO randtest VALUES (2, 'two.five');
INSERT INTO randtest VALUES (3, 'three.one');
INSERT INTO randtest VALUES (3, 'three.two');
INSERT INTO randtest VALUES (3, 'three.three');
INSERT INTO randtest VALUES (3, 'three.four');
INSERT INTO randtest VALUES (3, 'three.five');

I've got it working with using temporary tables:

DROP table t1; DROP table t2;

CREATE TEMPORARY TABLE t1 AS SELECT catnum, title, random() AS r FROM
randtest;
CREATE TEMPORARY TABLE t2 AS SELECT catnum, max(r) AS mr FROM t1 GROUP BY
catnum;
SELECT t1.catnum, t1.title, t1.r FROM t1, t2 WHERE t1.catnum = t2.catnum
AND t1.r = t2.mr;
catnum | title | r
--------+-----------+-------------------
2 | two.one | 0.576068660046937
3 | three.one | 0.695552298191726
1 | one.one | 0.988770103076831

catnum | title | r
--------+-------------+-------------------
1 | one.one | 0.602969813907039
3 | three.three | 0.851651187451394
2 | two.two | 0.904609308067993

catnum | title | r
--------+-------------+-------------------
2 | two.three | 0.659522682269813
3 | three.three | 0.697027135964961
1 | one.two | 0.895630815949119

The results seem pretty random per group. Is there a better way of
accomplishing this task without using temporary tables? Maybe with
subselects?

Thanks,
Thomas



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 11 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Thomas T. Thai" <to*@minnesota.com> writes:
CREATE TEMPORARY TABLE t1 AS SELECT catnum, title, random() AS r FROM
randtest;
CREATE TEMPORARY TABLE t2 AS SELECT catnum, max(r) AS mr FROM t1 GROUP BY
catnum;
SELECT t1.catnum, t1.title, t1.r FROM t1, t2 WHERE t1.catnum = t2.catnum
AND t1.r = t2.mr; The results seem pretty random per group. Is there a better way of
accomplishing this task without using temporary tables? Maybe with
subselects?


I'd suggest using DISTINCT ON (catnum) with ORDER BY catnum, random().
See the SELECT reference page's "weather reports" example for
background.

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

P: n/a
<quote who="Tom Lane">
I'd suggest using DISTINCT ON (catnum) with ORDER BY catnum, random().
See the SELECT reference page's "weather reports" example for
background.


Good suggestion. Thank you Tom.

Thomas

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.