CODEWITHSUNDEEP
mysql
ShyDonkey
ShyDonkey

Reputation: 51

Displaying values that occur consecutively

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

Upvotes: 5

Views: 148

Answers (3)

Racil Hilan
Racil Hilan

Reputation: 25351

Try this, no joins or sub-queries, just a simple grouping:

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

Here is a fiddle.

Upvotes: 0

Nir Levy
Nir Levy

Reputation: 12953

you can use join to see which entries has the next year's value, and then with distinct get the relevant id's:

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'm using inner join, we'll get records from a only if they exist in b- meaning if year-1 appears in your table for this directorId

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270693

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.

Upvotes: 5

Related Questions