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?

    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.



  • 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.)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2