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
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 |