CODEWITHSUNDEEP
mysqlsqljoinrandom
Chris
Chris

Reputation: 2344

SQL Join help when selecting random row

I have 2 tables; 'groups' and 'values'. I am trying to select one random row from the values table, where values.groupid = groups.id

The query I have is:

SELECT *
FROM groups as g
inner JOIN
(
SELECT * FROM values as v WHERE v.groupid = g.id ORDER BY RAND() LIMIT 1
) AS rv ON rv.groupid = g.id

v doesnt seem to have access to g how can i work around this? in regards to the random, I only managed to get the limit 1 solution to work, because there may be deleted value.id's and it is small table (only ever max 200 rows). if I only use join on, and dont have the where clause inside the join, it will select random value from any group, or return nothing. can anyone help me?

edit: this wont work either because I cannot access v from outside this time

SELECT g.*
,(SELECT * FROM v ORDER BY RAND() LIMIT 1)
FROM groups AS g
JOIN
(
  SELECT * FROM values
) AS v ON v.groupid = g.id

I see many other question very similar but, none actually try to do a join on the value for each group in effect .. if I am missing one please just mention and I will delete issue, i will continually look at others but in the mean time I am still needing help. thank you

Upvotes: 2

Views: 3845

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562310

I always avoid using ORDER BY RAND() with SQL, it causes terrible performance unless your dataset is really small. And if your dataset is that small, you'd still be better off fetching all the rows into your application and picking a random row.

Here's a solution that I just tested, and it works to pick one random row from each group:

SELECT g.*, v.*
FROM Groups AS g
INNER JOIN (
  SELECT groupid, COUNT(*) AS c, 1+FLOOR(RAND()*COUNT(*)) AS random 
  FROM `Values` GROUP BY groupid
) AS c ON g.id = c.groupid
INNER JOIN (SELECT @g:=0, @r:=0) AS init
INNER JOIN (
  SELECT IF(groupid<>@g, @r:=1, @r:=@r+1) AS rank, @g:=groupid AS groupid, value
  FROM `Values`
  ORDER BY groupid
) AS v ON g.id = v.groupid AND c.random = v.rank

Upvotes: 5

Chris
Chris

Reputation: 2344

SELECT u.login, a.filename FROM users u 
JOIN (SELECT filename, user_id, id AS rand_id FROM assets ORDER BY RAND()) a
ON u.id = a.user_id 
WHERE a.filename IS NOT NULL
GROUP BY user_id
ORDER BY u.login;

Upvotes: 0

Related Questions