2

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.

2 Answers 2

2

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

0

(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.

2
  • 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).
    – Googlebot
    Apr 10, 2022 at 16:22
  • 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.
    – Rick James
    Apr 10, 2022 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.