Hi Dan-
Thx for responding. And yes, I think you're absolutely correct. Let me update
that query - should look like:
<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
order by b.total_amt desc
limit 10)
order by a.k1, a.total_amt desc
;
</sql>
Anyone else?
Thanks,
R
-----Original Message-----
From: Dan Sashko [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 9:49 PM
To: Mysql
Subject: Re: Correlated subquery help
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]
