List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:January 17 2007 3:43pm
Subject:Re: Formatting a subquery?
View as plain text  
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
> 

Thread
Formatting a subquery?Brian Dunning16 Jan
  • Re: Formatting a subquery?Brent Baisley17 Jan
  • Re: Formatting a subquery?Brent Baisley17 Jan
Re: Formatting a subquery?Brian Dunning16 Jan
  • Re: Formatting a subquery?Michael Dykman16 Jan