SQL check adjacent rows for sequence

Question

I have a table with an id column (unique, primary), a name (not unique--in fact, most likely repeated), and a flag column which has values 0, 1, or 2. Let's say I reorder the table using the command

SELECT id, name, flag ORDER BY name, id

I want to produce using SQL a list of names where, when the rows in the reordering are read downward, there are two adjacent rows with the same name and flags of value 0 and 1 (in that order). Additionally, in that list, I want to have the ids of the two rows where this happened. If it happened more than once, there should be multiple rows.

So, for instance, in the following

id name flag
4  Bob  0
5  Bob  2
6  Bob  1
1  Cathy  0
7  Cathy  1
3  David  0
2  Elvis  2
8  Elvis  0
9  Elvis  1

I would want to select

name id1 id2
Cathy 1 7
Elvis 8 9

How do I do this?

I'm using MySQL.

EDIT: Note that the IDs for those adjacent rows might not be consecutive; they're only consecutive if we order by name. See, for example, Cathy.

Thanks!

Answer 1

try

select t1.name, t1.id as id1,t2.id as id2 
from tablename t1, tablename  t2 
where t1.flag = 0 and t2.id = t1.id+1 and t2.flag = 1 and t1.name = t2.name

Answer 2

Try:

select t1.name, t1.id as id1,t2.id as id2 
from tablename t1
join tablename t2 
on t2.name = t1.name and t2.flag = t1.flag + 1 and t2.id = 
    (select min(t3.id) from tablename t3
     where t1.name = t3.name and t1.id < t3.id)

EDIT: amended join to t2 to include lookup on subquery