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
