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
