With a name like Rene Fournier, shouldn't the subject line be "Je don't
think so?" ;-)
Okay, the first thing we need to know to help you is what version of MySQL
you are using. If I am not mistaken, subqueries aren't supported until
Version 4.1; if you are using 4.0 or earlier, your subquery won't work no
matter how you change it.
If you are using Version 4.1 or later, I don't see any real problem with
your subquery. However, since the subquery is getting a max(), you know that
it can only possibly return one value so you don't need to introduce it with
'in', an '=' will suffice, although both should work. However, it's possible
that MySQL is a bit flakey in this regard so try it with "=", i.e.
SELECT history.* FROM history
WHERE history.id =
(SELECT MAX(id) FROM history
WHERE account_id = 216
GROUP BY asset_id)
The 'history.*' shouldn't affect the ability of the query to run in any
case; that expression is just shorthand for "give me all of the columns in
the History table".
I can't try any of this in MySQL myself because I am still running MySQL
4.0.15. But your query should be fine as long as you have MySQL 4.1 or
later. It would certainly work in DB2, my main database.
--
Rhino
----- Original Message -----
From: "René Fournier" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Saturday, February 04, 2006 5:38 PM
Subject: MySQL says, "Ich don't think so" (Subquery woes)
SELECT MAX(id) FROM history
WHERE account_id = 216 GROUP BY asset_id
...works. Returns, e.g.: 1234, 3456, 5483, 8382.
SELECT history.* FROM history
WHERE history.id IN (1234, 3456, 5483, 8382 )
...works too. But if I try to combine them using a subquery, a la...
SELECT history.* FROM history
WHERE history.id IN ( SELECT MAX(id) FROM history WHERE account_id = 216
GROUP BY asset_id )
...it pretty much hangs MySQL. CPU goes to 100%, ten minutes later, I
have to kill the connection. I can't figure out why. Any ideas? Any
suggestions?
...Rene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 03/02/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 03/02/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
