9 messages in com.mysql.lists.mysqlRe: mysql top 2 rows for each group
FromSent OnAttachments
Vivian Wang28 Apr 2005 13:17 
Jay Blanchard28 Apr 2005 13:24 
mathias fatene28 Apr 2005 13:51 
SGr...@unimin.com28 Apr 2005 13:51 
mathias fatene28 Apr 2005 14:32 
Vivian Wang29 Apr 2005 06:28 
mfat...@free.fr29 Apr 2005 06:31 
Vivian Wang29 Apr 2005 06:54 
SGr...@unimin.com29 Apr 2005 07:08 
Subject:Re: mysql top 2 rows for each group
From:Vivian Wang (vivi@tcindex.com)
Date:04/29/2005 06:28:19 AM
List:com.mysql.lists.mysql

I don't have any PK in this table, but If you like one, I can add another column for PK, like, +----+-----------+ | id | seqno | +----+-----------+ | 1 | 000000122 | | 2 | 000000123 | | 3 | 000000123 | | 4 | 000000123 | | 5 | 000000336 | | 6 | 000000346 | | 7 | 000000349 | | 8 | 000000427 | | 9 | 000000427 | | 10 | 000000427 | +----+-----------+------+

I searched on internet before, there is some solution like this, set @temp1:=0 set @temp2:=0 update test set item=if(seqno=@temp2,@temp1:=@temp1+1, @temp2:= seqno); but this logic is not correct, does anyone know how to solve this one?

SGr@unimin.com wrote:

"Jay Blanchard" <jay.@niicommunications.com> wrote on 04/28/2005 04:24:23 PM:

[snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +-----------+ | 000000122 | | 000000123 | | 000000123 | | 000000123 | | 000000336 | | 000000346 | | 000000349 | | 000000427 | | 000000427 | | 000000427 | +-----------+------+

I like have +-----------+------+ | seqno | item | +-----------+------+ | 000000122 | 1 | | 000000123 | 1 | | 000000123 | 2 | | 000000123 | 3 | | 000000336 | 1 | | 000000346 | 1 | | 000000349 | 1 | | 000000427 | 1 | | 000000427 | 2 | | 000000427 | 3 | +-----------+------+

Then I can have select * from test where item <3 to find all top 2 rows. [/snip]

I think you want ...

SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2

I think that will result in only two rows total, not two per group.

Vivian? What is the PK for your table? What value or combination of values uniqely identifies each row of your source table? It can't be seqno as you already demonstrated that there are duplicate values in that column. I may have an idea but I need to know more about your data. Posting the results of SHOW CREATE TABLE xxx\G for your source table would be ideal.

Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine