Hi Roger- Thanks for responding. It's what I was afraid of - I essentially have to a write what amounts to a mini-batch process to get the info I want. In my specific case, it looks like I'd have to: 1)- Create a temp result table 2)- Get a list of distinct k1 3)- For each distinct k1, select the top ten into temp result table 4)- Use temp table to product result
And since I can't do 2) and 3) together because of the IN/LIMIT restriction, I'll have to craft a script/job/other to do it. Does anyone know if/when this restriction on use of IN and LIMIT will be supported? Thanks, R -----Original Message----- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Friday, December 03, 2004 10:32 AM To: Mysql Cc: [EMAIL PROTECTED] Subject: Re: Use of limit with IN on subquery Rick Robinson wrote: > 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. Sounds like a "group-wise maximum" type of question, take a look at this: http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]