Finding gaps between the values of a column in MySQL

Question

At a table of movie actors,

CREATE TABLE ArtistMap
(
ArtistID int(11) unsigned NOT NULL,
MovieID int(11) unsigned NOT NULL,
Year year,
INDEX(MovieID),
INDEX(Year),
PRIMARY KEY(ArtistID,MovieID)
) ENGINE=InnoDB

How can I select movies of an artist before a gap of X year in their works?

For example,

ArtistID MovieID Year
1 1 1985
1 2 1987
1 3 2000
1 4 2001
1 5 2002

I want to get the fields before a gap of 10 years in the artist's works (the gap occurs between 1987-200 when the artist did not play any movie). I want to get

ArtistID MovieID Year
1 1 1985
1 2 1987

If there is more than one gap, the first one suffices.

Answer 1

Use LEAD(year) or LAG(year) to discover a gap of 10+ years. Then use ROW_NUMBER() to keep rows before that gap. (See "windowing functions", available since MariaDB 10.2 or MySQL 8.0.)

Answer 2

(Not an answer, but a tip that might be useful.)

In MariaDB,

SELECT ...
 FROM seq_1985_to_2022 AS seq
 LEFT JOIN ArtistMap AS am ON am.Year = seq.num

will provide all the years in that range. Use seq.num for the "year"; am.Year will be NULL for the missing years. Therefore, you can fetch just the missing years by adding

WHERE am.Year IS NULL

Or maybe you want counts or blanks for the missing years. If so, use COALESCE(...) to turn NULL into 0 or blank.

Gap of 10 years -- Check the "gaps-and-islands" tag I added; it may help.

• Googlebot: if I'm not mistaken, it will catch any gap in the sequence, not a specific gap, e.g., X year (I mentioned 10 years in the example).
• Rick James: The pseudo-table provides all numbers in a desired range; after that, the LEFT JOIN and WHERE control what use you make of the "complete" set of numbers. If you wanted only 10 years starting with 1985, either use seq_1985_to_1994 or use, say, seq_0_to_9999 together with WHERE num BETWEEN 1985 AND 1994.