Need to output two records that are next to each other in a table that meet a criteria

I think this will work ...

Select M1.*, M2.* from
memos M1 Inner Join memos M2 ON
 M1.OperatorID <> M2.OperatorID AND
 M1.memo_system_text like 'Order DM14%' AND
 M2.memo_system_text like 'QA Order DM14%'
WHERE (Select count(*) from memos Where memo_id between M1.memo_id and M2.memo_ID) = 2

Select allOpen in new window

in the example you have given the memo_id values are not sequential. will them be sequential as you described or not? if not will we try to find out the next nearest row matching the criteria?

I was looking only for records that were next to each other, but allowing for gaps -- since the example obviously includes a gap.  Good question, tigin44 ... I might be disallowing too many matches.

sorry guys, my mistake. The memo_id values most likely will have a gap between them. The memo_id for the record with 'Order DM14% will always be less than the record with ''QA Order DM14%.

My table size is also about 2 million records.

Sorry for the delay is responding also.  I wll work on this issue as soon as I here from soneone.

Thanks

Will there be records (unrelated) that fill in those gaps?

no.  All other memos have been stripped from the table where the memo_system_text does not strat with 'Order DM14 % or 'QA Order DM14 %. Also the two records will always share the same account_nbr.  

The account_nbr for both matching records will always be the same
memo_id of first record will always be less than memo_id of second matching record
operator_id of record one will always be different that operator_id of record 2
memo_type of record 1 will always be 'A'. memo_type of record b will always be 'B'
memo_system_txt of record 1 will always start with 'Order DM14 % and have the same order number as matching record two
memo_system_txt of record 2 will always start with 'QA Order DM14 % and have the same order number as matching record one

Data Set results should look like:

Example output:
account_nbr     memo_id     operator_id     memo_type     memo_system_text
123456             2222265       123                    A                 Order DM14 - O - 1234567 was created
123456             3567981       777                    B                 QA Order DM14 - O - 1234567 was approved

333333             3975431      333                    A                 Order DM14 - O - 4444444 was created
333333            4012132       444                    B                 QA Order DM14 - O - 4444444 was approved

Cool.  Have you tried my code?

yes, I added an account_nbr in the where clause and ran it. Got no returns. Would the size of the table be a factor?

By no returns I mean after two hours the statement was still runnning

>>Would the size of the table be a factor?

Yes, it would.  I think my code is right, but you need more efficiency.  Let me see what I can do ...

thank you so much!!!!!!!!!

In the process of implementing your suggestios. Will advise of the results later today hopefully.

Indexed a test table and the query works excellent.  Thanks for all your help. Excellent work.