"Jay Blanchard" <[EMAIL PROTECTED]> 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
