Displaying values that occur consecutively

Question

I'm trying to display a list of all Directors who have directed 2 years consecutively.

Given the following data:

Pantomime table:
Year   titleID  DirectorID
2000    1         1
2001    2         7
2002    3         7
2003    4         8
2004    5         9
2005    6         9

This is the desired outcome:

DirectorID
   7
   9

This is the query I have tried so far but was unable to get the desired results.

SELECT directorID
FROM pantomime
where directorID = directorID+1
GROUP BY directorID

Answer 1

One method uses exists:

select distinct p.directorId
from pantomine p
where exists (select 1
              from pantomine p2
              where p2.directorId = p.directorId and p2.year = p.year + 1
             );

There are other fun variants on this idea, such as using in:

select distinct p.directorId
from pantomine p
where p.year in (select p2.year + 1
                 from pantomine p2
                 where p2.directorId = p.directorId
                );

And here is a totally arcane method that doesn't use join-like mechanisms at all (just aggregation):

select distinct directorId
from ((select directorId, year from pantomine)
      union all
      (select directorId, year + 1 from pantomine)
     ) p
group by directorId, year
having count(*) = 2;

This is also one of those really, really rare cases of using select distinct with group by.