Re: mysql top 2 rows for each group
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 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ 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 | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |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]
Re: mysql top 2 rows for each group
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 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ 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 | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |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]
Re: mysql top 2 rows for each group
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 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ 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 | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |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]
Re: mysql top 2 rows for each group
Vivian, Mathias was correct. I will be building on Mathias's answer by using your PK for your original data table (since you neglected to name it I will call it source_table) WITH THE ASSUMPTION that you cannot change your data structure and that you already have a PK on the table: Create a temporary table that counts how many records have each seqno (as Mathias said yesterday, this works because of a MySQL extension to auto_increment behavior and it only worked for him with a MyISAM table.) CREATE TEMPORARY TABLE tblSeqSort ( seqno int , seqcounter int auto_increment , id int , PRIMARY KEY (seqno, seqcounter) , UNIQUE (id) ); Then populate it in a particular order (the requirement of first two implies there is some kind of intrinsic order. I will sort by the ID value due to the lack of any other information about your data) INSERT tblSeqSort (seqno, id) SELECT seqno, id FROM source_table ORDER BY seqno, id; Then, just as Mathias demonstrated yesterday, you can get at just the first two records of each sequence by doing this SELECT st.id, st.seqno, other fields from source_table FROM source_table st INNER JOIN tblSeqSort sort ON sort.id = st.id AND sort.seqcounter =2; The drawback to this process is that you need to update your sequencing table each time you update your source_table or you could miss out on your most recently added source_table records. That can make this type of query time consuming and it may not be appropriate for many situations. It also requires more maintenance on your part. Mathias's solution (the simple solution) was to add the sequencing number to your original table. That way your data stays in sequence and the simple query is just what he proposed. This statement worked on a test table I have: ALTER TABLE source_table DROP PRIMARY KEY, ADD COLUMN seqcounter int auto_increment, ADD PRIMARY KEY (seqno, seqcounter); That makes your query: SELECT source_table field list FROM source_table WHERE seqcounter =2; Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 04/29/2005 09:31:23 AM: 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 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ 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 | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |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]
RE: mysql top 2 rows for each group
[snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql top 2 rows for each group
Hi ,
The table must be myisam. Innodb refused my solution which is here :
Beatifull
auto_increment
mysql create table seqs(seqno varchar(10) NOT NULL , id int
auto_increment, primary key (seqno,id)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
mysql
mysql
mysql insert into seqs(seqno) values('00122'),
-('00123'),
- ('00123'),
- ('00123'),
- ('00336'),
- ('00346'),
- ('00349'),
- ('00427'),
- ('00427'),
-('00427');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql select * from seqs;
+---++
| seqno | id |
+---++
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 | 3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 | 3 |
+---++
10 rows in set (0.00 sec)
- I like this type
of auto_increment
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: jeudi 28 avril 2005 22:24
To: Vivian Wang; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group
[snip]
I have question about how I can get top 2 rows for each group. like I
have table test
| seqno |
+---+
| 00122 |
| 00123 |
| 00123 |
| 00123 |
| 00336 |
| 00346 |
| 00349 |
| 00427 |
| 00427 |
| 00427 |
+---+--+
I like have
+---+--+
| seqno | item |
+---+--+
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 |3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 |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
--
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]
RE: mysql top 2 rows for each group
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 | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |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
RE: mysql top 2 rows for each group
For your query, just a where clause :
mysql select * from seqs where id 3;
+---++
| seqno | id |
+---++
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
+---++
8 rows in set (0.00 sec)
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: mathias fatene [mailto:[EMAIL PROTECTED]
Sent: jeudi 28 avril 2005 22:52
To: 'Jay Blanchard'; 'Vivian Wang'; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group
Hi ,
The table must be myisam. Innodb refused my solution which is here :
Beatifull
auto_increment
mysql create table seqs(seqno varchar(10) NOT NULL , id int
auto_increment, primary key (seqno,id)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
mysql
mysql
mysql insert into seqs(seqno) values('00122'),
-('00123'),
- ('00123'),
- ('00123'),
- ('00336'),
- ('00346'),
- ('00349'),
- ('00427'),
- ('00427'),
-('00427');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql select * from seqs;
+---++
| seqno | id |
+---++
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 | 3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 | 3 |
+---++
10 rows in set (0.00 sec)
- I like this type
of auto_increment
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: jeudi 28 avril 2005 22:24
To: Vivian Wang; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group
[snip]
I have question about how I can get top 2 rows for each group. like I
have table test
| seqno |
+---+
| 00122 |
| 00123 |
| 00123 |
| 00123 |
| 00336 |
| 00346 |
| 00349 |
| 00427 |
| 00427 |
| 00427 |
+---+--+
I like have
+---+--+
| seqno | item |
+---+--+
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 |3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 |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
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
