select random row from a group

From: "Thomas T(dot) Thai" <tom(at)minnesota(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: select random row from a group
Date: 2003-04-08 06:59:30
Message-ID: Pine.NEB.4.44.0304080157180.7952-100000@ns01.minnesota.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'd like to be able to select one random row from each group using one
select statement.

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 (2, 'two.one');
INSERT INTO randtest VALUES (2, 'two.two');
INSERT INTO randtest VALUES (2, 'two.three');
INSERT INTO randtest VALUES (3, 'three.one');
INSERT INTO randtest VALUES (3, 'three.two');
INSERT INTO randtest VALUES (3, 'three.three');

Something along the line of:

SELECT catnum, title, MAX(RAND()) as r
FROM randtest
WHERE 1=1
GROUP BY catnum;

--
Thomas T. Thai

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Nixon 2003-04-08 08:38:38 Re: extracting time from a timestamp with time zone field
Previous Message J. M. Brenner 2003-04-08 06:16:52 Re: Failed dependencies: perl(Pg) is needed by postgresql-contrib