CODEWITHSUNDEEP
mysqlsqlmaxwhere-clause
BradG
BradG

Reputation: 740

Add where condition in SQL randomizing query

Is it possible to add a WHERE condition in the below SQL query?

At the moment I am picking a random row from all rows into table and I need to change it so that i can get a random row only from the rows in table where column: type="1"

This is the SQL query:

SELECT *
  FROM table AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM table)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1;

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

Yes, you can add the condition. Do be careful, though, because you may not get any matches:

SELECT *
FROM table r1 JOIN
     (SELECT (RAND() * (SELECT MAX(id) FROM table)) AS id
     ) r2
WHERE r1.type = 1 and r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;

This might also have a big impact on performance. For performance, you would want an index on table(type, id). Also, you might as well include the where in the subquery as well:

SELECT *
FROM table r1 JOIN
     (SELECT (RAND() * (SELECT MAX(id) FROM table WHERE type = 1)) AS id
     ) r2
WHERE r1.type = 1 and r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;

Upvotes: 1

Related Questions