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]
