Home » SQL & PL/SQL » SQL & PL/SQL » sql query problem (Sql 10g)
sql query problem [message #440518] Mon, 25 January 2010 06:02 Go to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
Hi everyone,

i hope u all will be fine.

i have the following data in my table temp(c1 number(5))

15
16
17
20
21
22
23
27

i want to write a query to get skipped numbers e.g.
18
19
24
25
26

i hope u understand my question.

Regards
Athar
Re: sql query problem [message #440525 is a reply to message #440518] Mon, 25 January 2010 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> create table t as select distinct round(dbms_random.value(1,10)) seq from dual connect by level < 10;

Table created.

SQL> select * from t order by 1;
       SEQ
----------
         2
         3
         4
         6
         7
         9

6 rows selected.

SQL> select level from dual connect by level <= 10
  2  minus
  3  select seq from t
  4  order by 1
  5  /
     LEVEL
----------
         1
         5
         8
        10

4 rows selected.

If you post a working Test case: create table and insert statements along with the result you want with these data, we will work with your table and data.

Regards
Michel

[Updated on: Mon, 25 January 2010 06:38]

Report message to a moderator

Re: sql query problem [message #440534 is a reply to message #440525] Mon, 25 January 2010 07:23 Go to previous message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
Thanks a lot Dear Sir


I really appreciate your solution.

Its working for



Thanks once again.


Athar
Previous Topic: Extract duplicate records.
Next Topic: full outer join or union
Goto Forum:
  


Current Time: Thu Nov 14 04:26:47 CST 2024