473,382 Members | 1,439 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #1
2 2554
"Thomas T. Thai" <to*@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #2
<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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Jean-Francois.Doyon | last post by:
Hello, I'm trying to retrieve a limited number of random rows, and order them by a column, and am not having any luck with that last part: SELECT * FROM tablename ORDER BY random(), id LIMIT...
3
by: Neil Zanella | last post by:
Hello, I would like to ask the about the following... PostgreSQL allows tables resulting from subselects to be renamed with an optional AS keyword whereas Oracle 9 will report an error...
1
by: Mike L. Bell | last post by:
Query: update table1 t1 set end_time = ( select end_time from table2 t2 where t2.key1 = t1.key1 and t2.key2 = t1.key2 ) where exists
4
by: James | last post by:
I have a performance problem with the following query and variations on the subselect. The EXISTS version of the first example will complete in ~10 minutes. The NOT logic in both the examples...
4
by: Chris Kratz | last post by:
Hello all, We have run into what appears to be a problem with rules and subselects in postgres 7.4.1. We have boiled it down to the following test case. If anyone has any thoughts as to why...
2
by: Michael | last post by:
I am trying to INSERT only the first row returned from a subselect, is this possible? I'm on DB2 v8 on z/OS. Non-working Example: INSERT into some_table ( SELECT id FROM some_other_table...
2
by: awebguynow | last post by:
Most JS people have seen sorttable.js and similar implementations. I'm trying to do a SubSelect of an existing table, restricting values in a Column to spec. range. Rows manipulated through...
4
by: johnfaulkner | last post by:
Hi, I am trying to perform a single select of data from 2 tables, table A and table B. Table B may have none, one or many corresponding rows. If table B has no corresponding rows then table B...
5
by: Bart op de grote markt | last post by:
Hello, I have a problem with a subselect I use in a stored procedure: UPDATE #TEMP_TABLE SET P_ID_1=(SELECT top 1 b.P_ID_1 from #TEMP_TABLE b where b.ID=PARENT_ID), P_ID_2=PARENT_ID,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.