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?

Answers


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;

Need Your Help

How to selectively scrape html with repeated class IDs

python html xpath scraperwiki

I am new to python and have searched stackoverflow in vain for an answer that I can understand. Thanks in advance for any help or advice you can give.

Appending a specific object in an arraylist using java?

java object arraylist instance

What im trying to do is have the user input an index of "record" they want to append. The index will be used to look at a specific object in the array list. Everything stored in that object will be