Home » SQL & PL/SQL » SQL & PL/SQL » Oracle SQL Query
Oracle SQL Query [message #560801] Tue, 17 July 2012 04:21 Go to next message
satheesh_ss
Messages: 61
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 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
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
Re: Oracle SQL Query [message #560832 is a reply to message #560805] Tue, 17 July 2012 06:22 Go to previous messageGo to next message
Littlefoot
Messages: 19901
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>
Re: Oracle SQL Query [message #560842 is a reply to message #560832] Tue, 17 July 2012 07:01 Go to previous messageGo to next message
satheesh_ss
Messages: 61
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.
Re: Oracle SQL Query [message #560858 is a reply to message #560842] Tue, 17 July 2012 08:00 Go to previous messageGo to next message
joy_division
Messages: 4559
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.
Re: Oracle SQL Query [message #560859 is a reply to message #560842] Tue, 17 July 2012 08:01 Go to previous message
Michel Cadot
Messages: 60063
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
Previous Topic: SQL Query two columns
Next Topic: Rows of a table
Goto Forum:
  


Current Time: Sat Dec 27 14:07:52 CST 2014

Total time taken to generate the page: 0.07695 seconds