Announcement

Announcement Module
Collapse
No announcement yet.

slow query find gap between numbers

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • slow query find gap between numbers

    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?
Working...
X