displaying missing values

Results 1 to 3 of 3

Thread: displaying missing values

  1. #1
    Join Date
    Dec 1969

    Default displaying missing values

    hello,<BR>hope someone can help me with this.<BR>I have a table with an ID field that increments by 1.<BR>I need to find out which ID&#039;s are missing from the table.<BR>the table looks like :<BR>1<BR>2<BR>3<BR>4<BR>6<BR>7<BR>8<BR>9<BR>10<BR >12<BR>etc.<BR><BR>is it possible to display which numbers do not appear in the ID field ?<BR>

  2. #2
    Join Date
    Dec 1969

    Default You can either do this...

    ...programmatically, by looping through the records and checking whether they increment, something like this:<BR><BR>open connection<BR>open cursor<BR>counter = 1<BR>while not cursor.eof<BR>&nbsp;&nbsp;if cursor(ID) &#060;&#062; counter then<BR>&nbsp;&nbsp;&nbsp;&nbsp;message "Missing ID: " & counter<BR>&nbsp;&nbsp;&nbsp;&nbsp;counter = cursor(ID)<BR>&nbsp;&nbsp;end if<BR><BR>&nbsp;&nbsp;counter = counter + 1<BR>&nbsp;&nbsp;cursor.movenext<BR>wend<BR><BR>(o r rather than looping, use GetRows and loop through the array)<BR><BR>Or you can do it in SQL, something like this:<BR><BR>SELECT (ID+1) AS MissingID FROM MyTable WHERE (ID+1) NOT IN (SELECT ID FROM MyTable ORDER BY ID) ORDER BY ID<BR><BR>This can probably be optimized somehow, but the principle is to check for the next ID, i.e. ID+1, to see whether it exists, and if it doesn&#039;t we know it&#039;s missing - if that makes sense?<BR><BR>Oliver.

  3. #3
    Join Date
    Dec 1969

    Default Sure.

    Create a table of numbers, 1 to X.<BR><BR>Then, do an outer join against this table:<BR><BR>select numbers.ID<BR>from numbers<BR> left join datatable on numbers.ID = datatable.ID<BR>where datatable.ID is null

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts