Display values ​​that occur sequentially

Question

I am trying to display a list of all the directors who have sent 2 years in a row.

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 result:

 DirectorID 7 9 

This is a query that I have tried so far, but could not 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 interesting options in 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 completely secret method that does not use the union 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, very rare cases of using select distinct with group by .

Answer 2

you can use join to find out which records matter next year, and then get the corresponding identifiers with different ones:

 select distinct a.directorID from Pantomime as a inner join Pantomime as b on a.year = b.year-1 and a.directorID = b.directorID; 

since I use an internal join, we will only get records from them if they exist in b-value, if year-1 appears in your table for this DirectorId

Answer 3

Try this, no joins or subqueries, just a simple grouping:

 SELECT directorID FROM pantomime GROUP BY directorID HAVING COUNT(*) = 2 AND MAX(Year) = MIN(Year) + 1 

Here is the fiddle .