Re: mysql top 2 rows for each group

[Vivian Wang]

I did like this, look good, anyone can try it.
set @a:=0;
set @b:=0;
update test set item=if([EMAIL PROTECTED], @a:[EMAIL PROTECTED], if(@b:=seqno, 
@a:=1, 0));
Vivian Wang wrote:

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]