You could do something like this, not sure what your intent is if among
the top total_amt is a single exact amount that occurred 30 or 40
times...are you implying the top 10 items or the top 10 distinct items?
select *
from table_z a
where 10>=(select count(*)
from table_z b
where b.k1=a.k1
and b.total_amt>=a.total_amt)
Ed
-----Original Message-----
From: Rick Robinson [mailto:[EMAIL PROTECTED]
Sent: Friday, December 03, 2004 9:14 AM
To: 'Roger Baklund'; 'Mysql'
Subject: RE: Use of limit with IN on subquery
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
