OraFAQ Forum: SQL & PL/SQL » Oracle SQL Query

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle SQL Query

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Oracle SQL Query [message #560801]

Tue, 17 July 2012 04:21

satheesh_ss
Messages: 54
Registered: July 2012

Member

QUESTION: suppose we have values like 1 5 7 in a colum.Now we want numbers like(2 3 4 6) that exists between 1 5 7.How can we do this using sql query??

For Example;

Select * from one;

A
---
1
5
7

I want result like this

Select * from one;

A
---
1
2
3
4
5
6
7

Can anyone help me to get the correct query.

[Updated on: Tue, 17 July 2012 04:24]

Report message to a moderator

Re: Oracle SQL Query [message #560805 is a reply to message #560801]

Tue, 17 July 2012 04:35

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Sat, 14 July 2012 16:11

None.

From your previous topics:

Michel Cadot wrote on Thu, 12 July 2012 19:52
...
You not only have to read the Concepts manual but also the Database SQL Reference.

Regards
Michel

Michel Cadot wrote on Thu, 12 July 2012 15:11
From your previous topic:

Michel Cadot wrote on Thu, 12 July 2012 14:08
Please feedback to your previous topics and thanks people that (try to) help you.
Also from your previous topics:

Michel Cadot wrote on Thu, 12 July 2012 08:39
All your questions are answered in Database Concepts.
Please read it.

Regards
Michel

Michel Cadot wrote on Thu, 12 July 2012 08:47
...
Database Concepts

Regards
Michel

Regards
Michel

In addition, this is a FAQ already posted a hundred time, so SEARCH before posting (accordingly to the guide).

Regards
Michel

Report message to a moderator

Re: Oracle SQL Query [message #560832 is a reply to message #560805]

Tue, 17 July 2012 06:22

Littlefoot
Messages: 16935
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Keyword here is "row generator".

Your question is rather simple - you just need to know the maximum value and then generate all numbers less than that:

SQL> select * from test2;

       COL
----------
         1
         5
         7

SQL> select level from dual
  2  connect by level <= (select max(col) from test2)
  3  order by 1;

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7

7 rows selected.

SQL>

People usually ask for "missing numbers" - MINUS set operator might be useful in such a case:

SQL> select level from dual
  2  connect by level <= (select max(col) from test2)
  3  minus
  4  select col from test2
  5  order by 1;

     LEVEL
----------
         2
         3
         4
         6

SQL>

Report message to a moderator

Re: Oracle SQL Query [message #560842 is a reply to message #560832]

Tue, 17 July 2012 07:01

satheesh_ss
Messages: 54
Registered: July 2012

Member

Ya many peoples ask for the missing numbers...But my question was asked in TCS interview....Tats y i updated my question in forum...Anyway thanku for your answer Littlefoot.

Report message to a moderator

Re: Oracle SQL Query [message #560858 is a reply to message #560842]

Tue, 17 July 2012 08:00

joy_division
Messages: 4265
Registered: February 2005
Location: East Coast USA

Senior Member

satheesh_ss wrote on Tue, 17 July 2012 08:01

Ya many peoples ask for the missing numbers...But my question was asked in TCS interview....Tats y i updated my question in forum...Anyway thanku for your answer Littlefoot.

Did you spell like this on an interview too? Don't expect any job offers if you don't know common spelling or grammar.

Report message to a moderator

Re: Oracle SQL Query [message #560859 is a reply to message #560842]

Tue, 17 July 2012 08:01

Michel Cadot
Messages: 54129
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Ya many peoples ask for the missing numbers...But my question was asked in TCS interview....

And this prevents you from searching? I don't understand the point.

Once again read forum guide and do NOT use IM/SMS speak.
I think there is NO rule you didn't violate.

Regards
Michel

Report message to a moderator

Previous Topic:	SQL Query two columns
Next Topic:	Rows of a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 20 00:40:37 CDT 2013

Total time taken to generate the page: 0.10523 seconds