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 | 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]
> >
> >
>
>
