Обсуждение: Random via Subselect or Temporary Table

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

"Thomas T. Thai" <tom@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

<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