1

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!

unsorted
  • 3,114
  • 3
  • 29
  • 39

2 Answers2

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
Nik
  • 4,015
  • 3
  • 20
  • 16
  • Shouldn't t1 and t2 be linked by name, as well? –  Sep 21 '10 at 14:28
  • @Mark Bannister : Yes, you are correct. Thanks for mentioning – Nik Sep 21 '10 at 15:15
  • Thanks, I didn't know that you could select from a table multiple times like that. However, the code you wrote does not exactly solve the problem since the ids are not necessarily consecutive, they're only consecutive when we order by name. I clarified my example to try to make this more evident. – unsorted Sep 21 '10 at 17:29
  • Instead of selecting straight from t1, build a query which numbers the rows of t1 in order, then use that as an inline view or common table expression, and use Yogesh's logic on that - there, adjacent rows *will* have consecutive numbers. – Tom Anderson Sep 21 '10 at 17:46
1

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

  • Same comment as on Yogesh's... ids are not necessarily consecutive. Do I need to make a temp table or something? – unsorted Sep 21 '10 at 17:29