0

Given a table where each record has a year integer value, how do I query to see if there are 3 or more consecutive years?

| id | year |
+----+------+
| 1  | 1990 |
| 1  | 1991 |
| 1  | 1992 |
| 2  | 1973 |
| 3  | 1993 |
| 3  | 1994 |
| 3  | 1995 |
| 3  | 1996 |

Given the above table, the resulting records returned by the query would be:

| id |
+----+
| 1  |
| 3  |

The query would return ids 1 and 3 since those two records contain have consecutive years. How would I write such a query?

  • Wrong! Id #1 has only 2 consecutive years; 1992 is missing. Please restate the question. – Rick James Oct 22 '17 at 1:40
  • Oops, there you have it. – cheesebal35 Oct 22 '17 at 1:42
  • Would this count? 90,91,93,94? (I suspect not.) Or this? 90,91,93,94,85? (I suspect so.) – Rick James Oct 22 '17 at 4:10
1
SELECT DISTINCT
t.id
FROM tablename t
WHERE EXISTS (
    SELECT 1 FROM tablename x
    WHERE x.id = t.id
    AND x.year = t.year+1
)
AND EXISTS (
    SELECT 1 FROM tablename x
    WHERE x.id = t.id
    AND x.year = t.year-1
)
  • Where's the check against "3"? – Rick James Oct 22 '17 at 4:09
  • @RickJames There are three consecutive years in a row if for a given year there exists a year before and a year after that given year, for the same id. Seems valid to me, but it is late for me right now. – Willem Renzema Oct 22 '17 at 4:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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