Mahesh BS wrote:If you need to do this more than once, or if you have several missing numbers to find> Hello,
> I need to write a query to find out a set of missing number in a given
> Eg : a Column in some table has the following data
> 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.
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:
Then, you can issue a query like this:
LEFT JOIN sequence_table ON some_table.col_id = sequence_table.col_id
sequence_table.col_id IS NULL;
_ _ _ _
(_|| | |(_|>< The Data Charmer