Find the missing ticket numbers

Medium

Question

Support tickets are numbered 1, 2, 3, and so on. Some tickets were deleted, so the tickets table has gaps in the id column.

List every ticket number that is missing, from 1 up to the biggest id in the table. If the table starts at 3, then 1 and 2 are missing too.

Return one column, missing_id, with one row for each missing number. Sort by missing_id from low to high.

The order of the rows matters for this question.

recursive cte
anti join
sequence
gaps
not exists

Read first: Lesson 46, SQL queries inside queries: subqueries, EXISTS, CTEs and recursion

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

tickets2 columns · 9 rowsnot run yet

CREATE TABLE tickets (id INTEGER PRIMARY KEY, subject TEXT NOT NULL);

Example rows of the tickets table
id	subject
3	Login fails
4	Refund late
5	App crash
8	Wrong item
9	No OTP
12	Invoice copy
13	Address change
14	Slow app
17	Cancel order

About this SQL

SQLite has no generate_series() in this round, so build the list of numbers with a recursive CTE: a starting row, UNION ALL, and a part that adds 1 until a stop condition is true. Postgres and MySQL 8 use the same WITH RECURSIVE idea (Postgres also has generate_series). NOT IN is safe here because id can never be NULL; with a column that may hold NULL, NOT IN returns nothing, so use NOT EXISTS.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready