From: | "Thomas T(dot) Thai" <tom(at)minnesota(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Random via Subselect or Temporary Table |
Date: | 2003-08-09 00:39:46 |
Message-ID: | 1207.63.226.186.156.1060389586.squirrel@mail.minnesota.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Windsor | 2003-08-09 00:41:04 | Moving from MySQL |
Previous Message | Arguile | 2003-08-09 00:35:00 | Re: remove $ from money field |