how to query rows followed by specific rows

we have transaction data as below

id tcode

1 AB

2 AB

3 CD

1 EF

2 AB

2 EF

4 GH

3 AB

We want to query only rows with transaction code AB followed by transaction code EF. I mean, when we query the sample data above the result should contain only rows in bold.

You are going to find this to be virtually impossible.

There is NO guarantee that SQL Server will store consecutive entries in contigious space, e.g, row order cannot be relied upon -UNLESS you create a CLUSTERED index. And a clustered index will most certainly change the row sequence.

OR, there is another field in the table that indicates row order, i.e., an IDENTITY field.

|||

You can use the following query...But take care Arnie Rowland suggestions..

Code Snippet

select *,Identity(int,1,1) as RowId into #Data From data2

Select id,tcode From #Data
Where rowid in
(
Select A.RowId from #Data A
Join #Data B on A.RowId = B.RowId -1 And A.id=B.Id And A.tcode='AB' And B.tcode='EF'
Union
Select B.rowid from #Data A
Join #Data B on A.RowId = B.RowId -1 And A.id=B.Id And A.tcode='AB' And B.tcode='EF'
)