0

I have a database that contains basic information about instruments. Each instrument has in instrument id (000000-999999), that is its unique identifier. There are some holes in the instrument ids that i need to find. Is there a SQL query I can write to find where there are gaps in the instrument id column? It would be really helpful if I could find the largest holes (with the most instruments missing). I've searched around for an answer to this but it seems like most solutions involve two tables with a join, where this instance is only on 1 table. Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2576356
  • 77
  • 1
  • 1
  • 4
  • Thanks! The Martin Smith link worked perfectly. For anyone else who may want to use it, remove "WHERE status = 0" from the query if you don't need it. – user2576356 Dec 09 '13 at 18:05

1 Answers1

0

This can be a start:

;
WITH    Instrument  AS
(       SELECT  Id
        FROM    ( VALUES
                ( 01 ),
                ( 02 ),
                ( 03 ),
                ( 05 ),
                ( 09 ),
                ( 10 ) ) AS Sample (Id)

),      Sequence AS
(       SELECT  MIN(Id) Start,
                MAX(Id) Finish
        FROM    Instrument
        UNION ALL
        SELECT  Start + 1,
                Finish
        FROM    Sequence
        WHERE   Start < Finish
)
SELECT      Sequence.Start
FROM        Sequence
LEFT JOIN   Instrument
        ON  Sequence.Start = Instrument.Id
WHERE       Instrument.Id IS NULL
Rubens Farias
  • 57,174
  • 8
  • 131
  • 162
  • I'd be nervous about how a recursive CTE and a self-anti-semi-join will perform against a table with hundreds of thousands of rows. I think [the `ROW_NUMBER()` solution in the proposed duplicate](http://stackoverflow.com/a/17046749/61305) will scale a lot better. – Aaron Bertrand Dec 09 '13 at 17:50
  • I would probably use a tally table with a left join onto the data table. Any nulls where ID in the data table is match is your holes in your data. Very simple to understand instead of recursion. – CRAFTY DBA Dec 09 '13 at 19:58