Finding gaps between the values of a column in MySQL
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?
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.
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.)