Home » SQL & PL/SQL » SQL & PL/SQL » retrieve the missing numbers (retrieve the missing numbers)
retrieve the missing numbers [message #287936] Fri, 14 December 2007 01:29 Go to next message
deviji
Messages: 23
Registered: November 2007
Junior Member
Hi,



1. DESC TEAM
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER

2. SQL> select COL1 from TEAM;

COL1
----------
1
4
7
8
9
10

3. Question:

I need to retrieve the missing numbers (2, 3, 5, 6) from TEAM.

can you do this in ONE SQL statement
best wishes,
viji
Re: retrieve the missing numbers [message #287940 is a reply to message #287936] Fri, 14 December 2007 01:32 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
If the numbers are "missing" from where can you get them?
Re: retrieve the missing numbers [message #287945 is a reply to message #287936] Fri, 14 December 2007 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t order by 1;
       VAL
----------
         1
         4
         7
         8
         9
        10

6 rows selected.

SQL> with 
  2    minmax as ( select min(val) minval, max(val) maxval from t ),
  3    numbers as (
  4      select minval+level-1 num
  5      from minmax
  6      connect by level <= maxval-minval+1 
  7    )
  8  select num from numbers
  9  minus
 10  select val from t
 11  order by 1
 12  /
       NUM
----------
         2
         3
         5
         6

4 rows selected.

Regards
Michel
Re: retrieve the missing numbers [message #288015 is a reply to message #287945] Fri, 14 December 2007 05:45 Go to previous messageGo to next message
deviji
Messages: 23
Registered: November 2007
Junior Member
itz working fine
thank you
viji
Re: retrieve the missing numbers [message #288021 is a reply to message #287936] Fri, 14 December 2007 05:54 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

itz


I guess that should be 'It is'. Am I right?

Kiran.
Previous Topic: create external table
Next Topic: address query
Goto Forum:
  


Current Time: Wed Dec 07 03:03:05 CST 2016

Total time taken to generate the page: 0.06861 seconds