Select random row with other criteria

Question

I have seen this great answer on how to select a random row from a table and it works great on my table. Modifying that query I ended up with:

SELECT r1.clID, clUserName, clCompanyName, clBio 
FROM customerlogin AS r1 JOIN 
(
    SELECT 
    (
        RAND() * 
        (
            SELECT MAX(clID) 
            FROM customerlogin)) 
    AS clID) 
AS r2 
WHERE r1.clID >= r2.clID 
ORDER BY r1.clID ASC LIMIT 1

However I need to go one step further and limit the possible answers to those that match certain criteria.

I think the best way to do this would to be to build a temporary table, selecting only the valid rows from the original table, and then select a random row from the temporary table, however I am unsure on how to go about doing this. I've tried googling various combinations of create and select from random table, but with no joy so far. I'm assuming I just don't know the right way to ask what I'm after.

Can anybody please point me to a guide or some example code on how this can be accomplished? Or if there is a better solution I am overlooking then I am open to suggestions.

score 1 · Answer 1 · answered May 29 '13 at 14:42

The idea is to create a temporary table with an auto incrementing primary key. "Auto-incrementing" so it starts at 1 and is sequential. "Primary key" so you can use it to fetch rows very quickly.

Then load the table with the subset of data (or ids of the data) that you want. Then use ROW_COUNT() to get the number of rows in the table and rand() to fetch a random row.

The following code is an (untested) example:

create temporary table temp (
    id int auto_increment primary key,
    clid int
);

insert into temp(clid)
    select clid
    from customerLogin
    where <what you want>;

select @numrows := ROW_COUNT();

select @therow := (@numrows - 1) * rand();

select cl.*
from (select temp.*
      from temp
      where id = @therow
     ) temp join
     CustomerLogin cl
     on cl.clid = temp.clid;

Hmm... it seems my host doesn't allow this. Very strange. As soon as I try to run this I get access denied for user. Thank you for taking the time to show me this though. — Styphon, May 29 '13 at 16:07

score 1 · Accepted Answer · answered May 29 '13 at 16:12

As long as your criteria is staying static, you could just create a view.

Something like this for the view:

CREATE VIEW customerloginVIEW 
AS  SELECT clID,  clUserName, clCompanyName, clBio 
FROM customerlogin
WHERE something = somethingelse
GROUP by clID
ORDER BY clID DESC

and the query

SELECT r1.clID, clUserName, clCompanyName, clBio 
FROM customerloginVIEW AS r1 JOIN 
(
    SELECT 
    (
        RAND() * 
        (
            SELECT MAX(clID) 
            FROM customerloginVIEW)) 
    AS clID) 
AS r2 
WHERE r1.clID >= r2.clID 
ORDER BY r1.clID ASC LIMIT 1

score 0 · Answer 3 · answered May 29 '13 at 14:42

I would just get a list of possible choices, pick a random one, then fetch it. Something like below:

/* Get list of possible choices */
SELECT clID
FROM customerlogin
WHERE ("match criteria here")

// Use a server-side language to pick a random number from this list?

/* Reissue query to fetch the chosen random id */
SELECT *
FROM customerlogin
WHERE clID = ("chosenRandomID")

Or, it looks like you could simply modify the select statement that your randomizer chooses from:

SELECT r1.clID, clUserName, clCompanyName, clBio 
FROM customerlogin AS r1 JOIN 
(
    SELECT 
    (
        RAND() * 
        (
            SELECT MAX(clID)
            FROM customerlogin
            WHERE ("match criteria here"))) 
    AS clID) 
AS r2 
WHERE r1.clID >= r2.clID 
ORDER BY r1.clID ASC LIMIT 1

Modifying the statement doesn't work. That just limits the maximum ID. You can still get incorrect results if there are ID's that don't match the criteria lower than the highest ID that does match the criteria. — Styphon, May 29 '13 at 19:41

Select random row with other criteria

3 Answers3