SQL Query for gap in sequential field

[impulse24]

Hi,

I received a database that has a field named Certificate Number, and the field is supposed to have sequential certificate numbers. However I have noticed there are gaps. There is cert # 1,2,3,4,5,9,10

Is there are query that I can do to show me what the gaps are: Example from above would show that 6,7,8 are missing. Any help would be appreciated.

 

[carp]

I built a table called TEST with a single column X. It is populated with the values 1,2,4. The following query works (almost):

SQL> select x+1 from test a
2 where not exists (SELECT x FROM test b WHERE b.x = a.x+1)
3 order by 1;

X+1
----------
3
5

Note that it includes the value 5, which is max(x) + 1. As long as you strip off the highest value, I think this will do the job for you.

 

[carp]

On further reflection, I realize that this query will only detect the presence of a gap - it won't tell you how large the gap is. For instance, if my table had the values 1,2,5,6 the query would yield the answer 3,7 - the 7, of course being due to the fact that the highest number in the table is 6. Note that while the query detected the gap at 3, it did NOT detect that 4 is also missing.

 

[EnglishMan]

impulse24,

The only way I could think to show all the values in the gaps would be to create a table with all possible values in the table for the Certificate Number. Then you could do the following query ...

SELECT A.X
FROM Table2 AS A
WHERE not exists (SELECT A FROM TABLE1 b WHERE b.A = A.X);

Where table2 contains all possible values for the certificate number and table1 contains your data.

Not sure this will help.
Englishman

 

[Tim1]

Hiya,

If your table starts from one, you could try:

DECLARE @value INT,
@max_value INT

SELECT @value = 1

SELECT @max_value = MAX(identity_column)
FROM table

WHILE @value < @max_value
BEGIN

SELECT 1
FROM table
WHERE identity_column = @value

IF @@rowcount = 0 -- No rows found with that value
PRINT '%1!',@value

SELECT @value = @value + 1
END

That should do the job for you

Tim