Re: Correlated subquery help

[Dan Sashko]

isn't the where subquery would always return only one record if set of 
(k1,k2) is a primary key?
I dont have 4.1+ installed to test on but if you remove 'limit 10' and run 
it don't you get the same list as if you ran 'select k1,k2,total_amt from Z' 
?

----- Original Message ----- 
From: "Rick Robinson" <[EMAIL PROTECTED]>
To: "Mysql" <[EMAIL PROTECTED]>
Sent: Wednesday, December 01, 2004 6:08 PM
Subject: Correlated subquery help


Hi all-
I'm using MySQL 4.1.7, trying to do a subquery that's apparently 
unsupported -
I'm hoping someone can provide a quick alternative for me.

I have a simple table Z with 3 columns, k1, k2, and total_amt, where k1 
and k2
make up the primary key.  I want to create a report that lists the the top 
10
total_amt for each k1, k2.  My original query was going to be of the form:
<sql>
select
   a.k1,
   a.k2,
   a.total_amt
from Z a
where a.total_amt in
   (select b.total_amt
    from Z b
    where b.k1 = a.k1 and
    b.k2 = a.k2
   order by b.total_amt desc
   limit 10)
order by a.k1, a.total_amt desc
;
</sql>
But I get the dreaded error: ERROR 1235: This version of MySQL doesn't yet
support 'LIMIT & IN/ALL/ANY/SOME subquery'

Is there a better way to do this query?
Thanks for your help.
Regards,
R

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]