Thread

1. Random via Subselect or Temporary Table

   Thomas T. Thai <tom@minnesota.com> — 2003-08-09T00:39:46Z

   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
   
   
   
   
   
   
   
   

2. Re: Random via Subselect or Temporary Table

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-08-09T03:17:31Z

   "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
   
   

3. Re: Random via Subselect or Temporary Table

   Thomas T. Thai <tom@minnesota.com> — 2003-08-09T03:44:20Z

   <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