Don't bother. This is a very expensive solution. You get nearly a
Cartesian product on each JOIN. I've got a 40 row test table with 20 values
in each of 2 groups. The top 3 version of this examines 2302 rows to
produce the 3 values for each of the 2 groups. The top 10 version has been
running for several minutes...
Michael
Rick Robinson wrote:
Hi Roger-
Thanks for responding so quickly.
Hmm. I like it. It would get a bit hairy if I wanted top 50 or top 100. And
if I wanted the top # to be dynamic, I'll need to construct the query on the
fly...but that may be very workable. I need to play with it a bit to see how it
will perform. I have a table with about 500,000 rows (not really too big), so
I'm hopeful.
Thanks again.
Best regards,
Rick
-----Original Message-----
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: Friday, December 03, 2004 10:49 AM
To: Mysql
Cc: [EMAIL PROTECTED]
Subject: Re: Use of limit with IN on subquery
Rick Robinson wrote:
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.
Hm... "top 10"... group-wise maximum is maybe not the best approach.
Maybe a self-join is better in this case. A small experiment:
use test;
create table tt2 (
k1 char(1) not null,
k2 int not null,
total_amt int,
primary key(k1,k2));
insert into tt2 values('a',1,412),('a',2,142),('a',3,123),('a',4,312);
insert into tt2 values('b',1,441),('b',2,251),('b',3,421),('b',4,331);
select * from tt2;
+----+----+-----------+
| k1 | k2 | total_amt |
+----+----+-----------+
| a | 1 | 412 |
| a | 2 | 142 |
| a | 3 | 123 |
| a | 4 | 312 |
| b | 1 | 441 |
| b | 2 | 251 |
| b | 3 | 421 |
| b | 4 | 331 |
+----+----+-----------+
8 rows in set (0.02 sec)
select t1.k1,
max(t1.total_amt) first,
max(t2.total_amt) second,
max(t3.total_amt) third
from
tt2 t1
left join tt2 t2 on
t2.k1 = t1.k1 and
t2.total_amt < t1.total_amt
left join tt2 t3 on
t3.k1 = t1.k1 and
t3.total_amt < t2.total_amt
group by
t1.k1;
+----+-------+--------+-------+
| k1 | first | second | third |
+----+-------+--------+-------+
| a | 412 | 312 | 142 |
| b | 441 | 421 | 331 |
+----+-------+--------+-------+
2 rows in set (0.05 sec)
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
