Thread: Query to obtain missing number

1. Registered User
Join Date
Oct 2004
Location
Pittsburgh, PA
Posts
10

Unanswered: Query to obtain missing number

I've been trying to figure out how to create a query that would list the missing numbers between a high and low number for a field. For example, If I have the recordset below:

1
3
4
6
7
9

I'd like the resulting recordset to be:

2
5
8

Is there a way to achieve this? Thanks, Jason.

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Yes, there are several ways.

What have you covered so far in class?

-PatP

3. Registered User
Join Date
Oct 2004
Location
Pittsburgh, PA
Posts
10

Class?

In Class? I'm not taking a class. I know the programming language fairly well, I just cannot figure this one out. Can you give me a quick example? Thanks, Jason.

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
There are multiple ways to do this. Probably the simplest is to create a "numbers" table with one row for every interesting (possible) value that a number might have. For a two byte integer, this range could be -32768 through 32767. Once you've got the numbers table, you can do a simple exists test, something like:
Code:
```SELECT n.val
FROM numbers AS n
WHERE NOT EXISTS (SELECT *
FROM myRecordset AS r
WHERE  r.val = n.val)```
Of course you'd also need to limit the result to just the values of interest in this case (between the Min and Max values already in your recordset).

-PatP

5. Registered User
Join Date
Oct 2004
Location
Pittsburgh, PA
Posts
10
I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!

6. Registered User
Join Date
Oct 2004
Location
Pittsburgh, PA
Posts
10

Cannot do that :-(

I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!

7. Registered User
Join Date
Oct 2004
Location
Pittsburgh, PA
Posts
10

Cannot do that :-(

I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!

8. Registered User
Join Date
Oct 2004
Location
Pittsburgh, PA
Posts
10

Cannot do that :-(

I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!

9. Registered User
Join Date
Aug 2004
Posts
330
Does Foxpro support recursive queries? If so, you could recursively increment an integer up to some limit and exclude the non-qualifying rows.

Posting Permissions

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