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.
If we step through each query we should come up with something workable.
Get the people:
----- Original Message -----
From: "Brian Dunning" <[EMAIL PROTECTED]>
To: "mysql" <mysql@lists.mysql.com>
Sent: Tuesday, January 16, 2007 2:59 PM
Subject: Re: Formatting a subquery?
Hmmm, I see the problem. I can't think of any other way to do it. I have a table of people, and I want to return 20 random
people. Each person has multiple picture records, and I want to return a random picture for each. That's why I'm trying to put
the 'order by rand()' in the subquery: if I make it a single query and try to do a 'group by' it always gives the same picture
for each person.
On Jan 16, 2007, at 11:55 AM, Rhino wrote:
----- Original Message ----- From: "Brian Dunning" <[EMAIL PROTECTED]>
To: "mysql" <mysql@lists.mysql.com>
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();
I haven't used MySQL 5 and I'm not sure what it will tolerate but in DB2 SQL, which I have used for many years, you cannot do an
ORDER BY within a subquery. Only the other query can contain an ORDER BY. (Then again, I am not completely up-to-date on the SQL
standards any more so maybe DB2 V9 will even tolerate an ORDER BY in the subquery.)
You may also be having trouble because of the LIMIT clause in the subquery.
In any case, I think the ORDER BY in the subquery is redundant: you shouldn't care what order the rows in the subquery are read,
just what order is used to PRESENT them in the final query.
--
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
