Of course you realize it's the ORDER BY in the subquery that is giving you greif. I don't see why you wouldn't get the same net effect if you remove the ORDER BY from the inner query and leave it on the outer one.. to my eye, it looks like you would get the same randomized result.
On 1/16/07, Brian Dunning <[EMAIL PROTECTED]> wrote:
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]
-- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
