I have a table invoices with a field invoice_number. This is what happens when i execute select invoice_number from invoice
| invoice_number |
|---|
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 10 |
| 11 |
I want a SQL that gives me the following result:
| gap_start | gap_end |
|---|---|
| 1 | 3 |
| 5 | 6 |
| 10 | 11 |
-
You should not use a sequence for invoice numbers, it's normal for a sequence to have "gaps".Frank Heikens– Frank Heikens2021-02-16 10:59:09 +00:00Commented Feb 16, 2021 at 10:59
-
Does this answer your question? Find gaps of a sequence in SQL without creating additional tablesBergi– Bergi2023-09-13 23:02:21 +00:00Commented Sep 13, 2023 at 23:02
Add a comment
|
1 Answer
You can use row_number() window function to create a row count and use the difference to your actual values as group criterion:
SELECT
MIN(invoice) AS start,
MAX(invoice) AS end
FROM (
SELECT
*,
invoice - row_number() OVER (ORDER BY invoice) as group_id
FROM t
) s
GROUP BY group_id
ORDER BY start
