2

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

pilcrow
  • 56,591
  • 13
  • 94
  • 135
Chris
  • 2,340
  • 6
  • 40
  • 63
  • I think this is solution, I am just trying now http://recursive-design.com/blog/2009/08/11/mysql-select-random-row-with-join/ – Chris Dec 07 '11 at 19:18
  • Is there some "primary" key identifier in the values table, if so, what is it's column name. – DRapp Dec 07 '11 at 21:09

2 Answers2

5

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
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I feel I understand 90% of this query, it is very clever of you to provide a better performing query, and I appreciate it greatly. The query is quite long, and a little hard to read which could make it difficult when it is expanded upon, but still brilliant, however you have already made good point about putting the load onto the application, and I will test all 3 solution as performance can definitely be a game changer when your database is very active. thank you, c – Chris Dec 07 '11 at 20:01
0
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;
msarchet
  • 15,104
  • 2
  • 43
  • 66
Chris
  • 2,340
  • 6
  • 40
  • 63