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]
