Random via Subselect or Temporary Table
От | Thomas T. Thai |
---|---|
Тема | Random via Subselect or Temporary Table |
Дата | |
Msg-id | 1207.63.226.186.156.1060389586.squirrel@mail.minnesota.com обсуждение исходный текст |
Ответы |
Re: Random via Subselect or Temporary Table
|
Список | 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
В списке pgsql-general по дате отправления: