Sorry about the last email, sent it before I finished it. As I was saying...
Just so I'm straight on this. You want to select 20 random people, then select one random
picture for each of those 20 people,
ending up with 20 random pictures, each from a different account
Your query I'm pretty sure gives you all pictures from the 20 random accounts, ordered
randomly. When you just want 1 random picture
for each account.
I think if you just add another select with a group by, you should have what you want.
select account_id,picture_id FROM (
select account_id,picture_id from pictures where account_id in
(select account_id from accounts order by rand() limit 20)
order by rand()
) as pics group by account_id
----- Original Message -----
From: "Brian Dunning" <brian@stripped>
To: "mysql" <mysql@stripped>
Sent: Tuesday, January 16, 2007 2:23 PM
Subject: Formatting a subquery?
>I can't get MySQL 5 to like this query. Can you tell what I'm trying to do, and is
> there a problem with my formatting?
> select account_id,picture_id from pictures where account_id in
> (select account_id from accounts order by rand() limit 20)
> order by rand();
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1