Generating the series of number with simple sql query

Today I came across this thread at Oracle Forum.

Where Question was as follows:
Consider Test table have Numb (Number datatype) field. table values are given below.

Table Name: Test
NUMB
1
2
4
7
8
9
12 … This table have more than thousand records like that…..

How to retrive the missing numbers. I’m expecting the output 3,5,6,10,11……..
Could you please give the SQL for this task. Thanks!

One of the member David Grimberg gave following solution.

select level numb from dual connect by level <= (select max(numb) from test)
minus
select numb from test;

I was very impress with this sql because I recognize the worth of this sql to myself and many situation in which I am stuck many times.

So many time I am stuck with a situation that we need to write a query which should generate sequence number.

This solution never came to my mind as most of the time I resorted to pipeline function or some dummy table.

Now with the help of David’s solution it can be easily generated with simple sql as follows:

select level seq from dual connect by level <=

Example:

query_sql20080326_01

The simplicity of the solution really impressed me!

Thanks David for sharing it and hope that it will be useful to others!

Bookmark this post

Hide

Tags: SQL and PL/SQL

1 joeB // Mar 28, 2008 at 4:43 am

I wrote this script some time ago to allow me to match events to the days of the month…

SELECT all_dom.theday, event, event_id, begin_dt, end_dt
FROM (SELECT matchday, event, event_id, begin_dt, end_dt
FROM (SELECT LEVEL matchday
FROM DUAL
CONNECT BY LEVEL
2 Rajender Singh // Mar 28, 2008 at 1:07 pm

Hi Joe,

Seem to be interesting.

But I am getting following error when I am running the innermost query.

SQL> connect scott/tiger
Connected.
SQL> SELECT LEVEL matchday
2 FROM DUAL
3 CONNECT BY LEVEL;
CONNECT BY LEVEL
*
ERROR at line 3:
ORA-00920: invalid relational operator

Thanks & Regards
3 ramesh // Aug 13, 2008 at 2:21 pm

query to display all employees record who are hired in month of february
in employees table

Recent Posts