Query to obtain missing number

jimithing1980 · #1 (**permalink**) 10-06-04, 15:29

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.

Pat Phelan · #2 (**permalink**) 10-06-04, 15:47

Yes, there are several ways.

What have you covered so far in class?

-PatP

jimithing1980 · #3 (**permalink**) 10-06-04, 16:02

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.

Pat Phelan · #4 (**permalink**) 10-06-04, 16:15

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

jimithing1980 · #5 (**permalink**) 10-06-04, 16:45

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!!

jimithing1980 · #6 (**permalink**) 10-06-04, 16:45

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!!

jimithing1980 · #7 (**permalink**) 10-06-04, 16:46

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!!

jimithing1980 · #8 (**permalink**) 10-06-04, 16:51

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!!

urquel · #9 (**permalink**) 10-07-04, 09:32

Does Foxpro support recursive queries? If so, you could recursively increment an integer up to some limit and exclude the non-qualifying rows.

Internet Services

Web Development

Digital Marketing

Consumer Tech