Hi,

I was wondering what would be the best way to use SQL to track for missing
numbers in a table within a certain range.

ie, let's say I have a table with a field called SEQ

What I would like to do is test to make sure that there isn't a missing SEQ
number (or double-up) in that field between two numbers.

In this table, some records will not hold a SEQ number at all (and this is
permitted) so we're only looking for records with a SEQ number.

I thought of doing a query between two numbers, and then performing a
recordcount but this won't work if there is a double-up of numbers.

ie: if I was to go between 1 and 10, and I have the following SEQ records:

1
2
3
3
5
6
7
8
9
10

There are still 10 records here, but 3 is double up'd and 4 is missing
completely.

Just wondering if there's a neat simple way to perform this check in SQL?

Cheers

Adam.

Adam

> What I would like to do is test to make sure that there isn't a missing
SEQ
> number (or double-up) in that field between two numbers.
>
> In this table, some records will not hold a SEQ number at all (and this is
> permitted) so we're only looking for records with a SEQ number.
>
> I thought of doing a query between two numbers, and then performing a
> recordcount but this won't work if there is a double-up of numbers.

If you want to see the start of any hole then

select distinct s.id + 1 as start
from seq s
 left outer join seq t on s.id + 1 = t.id
where t.id is null and (s.id not in (select max(id) from seq))

If you want to see the start and stop of any hole (this performs almost a
cartesian join s.id < u.id so could be very slow for large datasets).

select s.id + 1 as start, min(u.id) - 1 as stop
from seq s
   left outer join seq t on s.id = t.id - 1
   left outer join seq u on s.id < u.id
where s.id is null and u.id is not null
group by start, stop

If you want to see duplicates

select id,count(id) as cnt from seq
group by id
having cnt > 1

John

Hi John,

Sorry for the delayed reply.

Fantastic - thank you very much for your response, and various examples.
Greatly appreciated!

Best Regards

Adam.

> If you want to see the start of any hole then
>
> select distinct s.id + 1 as start
> from seq s
>  left outer join seq t on s.id + 1 = t.id
> where t.id is null and (s.id not in (select max(id) from seq))
>
> If you want to see the start and stop of any hole (this performs almost a
> cartesian join s.id < u.id so could be very slow for large datasets).
>
> select s.id + 1 as start, min(u.id) - 1 as stop
> from seq s
>    left outer join seq t on s.id = t.id - 1
>    left outer join seq u on s.id < u.id
> where s.id is null and u.id is not null
> group by start, stop
>
> If you want to see duplicates
>
> select id,count(id) as cnt from seq
> group by id
> having cnt > 1
>
> John
>
>

John;
Was lurking and read your response.
This is great.

Thanks from me as well

rEGARDS

Allan Brocklehuirst

"John Hay" wrote:

Adam

> What I would like to do is test to make sure that there isn't a missing
SEQ
> number (or double-up) in that field between two numbers.
>
> In this table, some records will not hold a SEQ number at all (and this is
> permitted) so we're only looking for records with a SEQ number.
>
> I thought of doing a query between two numbers, and then performing a
> recordcount but this won't work if there is a double-up of numbers.

If you want to see the start of any hole then

select distinct s.id + 1 as start
from seq s
 left outer join seq t on s.id + 1 = t.id
where t.id is null and (s.id not in (select max(id) from seq))

If you want to see the start and stop of any hole (this performs almost a
cartesian join s.id < u.id so could be very slow for large datasets).

select s.id + 1 as start, min(u.id) - 1 as stop
from seq s
   left outer join seq t on s.id = t.id - 1
   left outer join seq u on s.id < u.id
where s.id is null and u.id is not null
group by start, stop

If you want to see duplicates

select id,count(id) as cnt from seq
group by id
having cnt > 1

John