Hi, have you read my answer yesterday ? Mathias
Selon Vivian Wang <[EMAIL PROTECTED]>: > 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([EMAIL PROTECTED],@temp1:[EMAIL PROTECTED], @temp2:= > seqno); > but this logic is not correct, does anyone know how to solve this one? > > [EMAIL PROTECTED] wrote: > > >"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 > > > > > > > > > > > > > -- > 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]
