Re: Random via Subselect or Temporary Table

"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