HI.
I have a problem with a query. I must find the gap between numbers in a table. (ex 1, 2, 4, 5, cool: I need 3,6, … or something like that. After a little bit of searching i came to this query:
SELECT l.number + 1 as start FROM invoice AS l LEFT OUTER JOIN invoice AS r ON l.number + 1 = r.number WHERE r.number IS NULL;
OR THIS QUERY
select l.number + 1 as start, min(fr.number) - 1 as stop
from invoice as l
left outer join invoice as r on l.number = r.number - 1
left outer join invoice as fr on l.number < fr.number
where r.numberd is null and fr.number is not null
group by l.number, r.number;
The problem is that the query is to slow on 70000 records. Is there a better way?