Getting random rows based on column index
As of right now the table setup is like so:
CREATE TABLE testing ( id INT NOT NULL, text TEXT NOT NULL ); CREATE TABLE users ( id INT NOT NULL, name VARCHAR(32) NOT NULL ) UNIQUE KEY (id);
This essentially stores quotes from people, and what I would like to do is have a random quote on each query. As of right now, I am able to minimize it down to selecting a single user and a quote via:
SELECT name, MIN(text) FROM testing INNER JOIN users ON users.id = testing.id GROUP BY name
However, this will not select a random text each time if there are multiple. How can I support getting a different one each time without duplicate rows?
You can do this using the row_number() and random() functions:
select name, text from (SELECT name, text, row_number() over (partition by name order by random()) as seqnum FROM testing t INNER JOIN users u ON u.id = t.id ) t where seqnum = 1;