Hello,



I need to write a query to find out a set of missing number in a given
sequence.



Eg : a Column in some table has the following data



Col1

1

2

3

4

5

6

8

9

10



Here I need to write a query to find out that number 7 is missing in the
given sequence.

One possible solution is by using any loop. But I am looking out if the same
can be achieved using any query.



Thanks in advance.



Regards,

Mahesh

Mahesh BS wrote:
> Hello,
>
>
>
> I need to write a query to find out a set of missing number in a given
> sequence.
>
>
>
> Eg : a Column in some table has the following data
>
>
>
> Col1
> 1
> 2
> 3
> 4
> 5
> 6
> 8
> 9
> 10
>
>
>
> Here I need to write a query to find out that number 7 is missing in the
> given sequence.
>
> One possible solution is by using any loop. But I am looking out if the same
> can be achieved using any query.
>

If you need to do this more than once, or if you have several missing numbers to find
in a large dataset, here's the method:
Create a table that contains a sequence, and use a LEFT JOIN to find the missing one.

To create a table and fill it with numbers quickly, use the method described in this article:
http://datacharmer.blogspot.com/2006...s-quickly.html

Then, you can issue a query like this:

SELECT
some_column
FROM
some_table
LEFT JOIN sequence_table ON some_table.col_id = sequence_table.col_id
WHERE
sequence_table.col_id IS NULL;

ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
http://datacharmer.org/