recurser

I have a users table and an assets (basically just images) table, and I want to select and display a random asset for each user. I want to show a list of users, with a random thumbnail for each user from their assets.

I could do this easily enough in code with multiple queries, but it seemed like a nice challenge to try and find a pure-sql alternative.

First attempt

This was based on an idea from the mysql forums :

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

Since the ‘order by rand()’ happens after the ‘group by’, it doesn’t return random results – it always returns the same results, but in random order.

Second attempt

Apparently this is known as the MAX-CONCAT trick :

SELECT u.login, user_id, SUBSTRING(MAX(CONCAT(TRUNCATE(RAND(),4),a.id)),7) AS asset_id 
FROM assets a 
LEFT JOIN users u ON a.user_id = u.id 
WHERE user_id IS NOT NULL
GROUP BY user_id 
ORDER BY user_id;

This works, however it’s pretty hacky and very MySQL-specific, and I doubt it will work on any other databases. Depending on the number of rows you want to return, it could well end up being faster just doing the heavy lifting in code rather than trying to bend SQL to your will.

Update – Solution (2009/12/04)

Ronnii commented and pointed out this approach which (as far as i can tell) seems to do exactly what i wanted.

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;

Thanks Ronni!