Re: How do I retrieve free values?

On Thu, 22 Aug 2002 10:18:01 +0200, "Lars Reineke" <reineke_at_kreiskrankenhaus-hameln.de> wrote:

>Given a table A with a column NUMBER, which stores numerical values, let's
>say in a range from 1 to 9999, how do I retrieve the values that are in that
>range but not stored into that table?

How do you retrieve values that don't exist? In the general case, you can't. Because they don't exist.

But you can take advantage of the fact that you're looking at integers, and derive a set based on the existing set of integers. Very roughly . . .

 SELECT T1.[Number] + 1 AS GapStart
 FROM SomeTable as T1
 WHERE T1.[Number] + 1 NOT IN
   (SELECT T2.[Number] FROM SomeTable AS T2)

This identifies the *start* of a gap in the sequence--the first value in a sequence of missing values. It ignores boundary issues.

The simplest way to identify *every* missing value is to do a frustrated outer join between your table and a table of valid values.

-- 
Mike Sherrill
Information Management Systems