aspose file tools*
The moose likes Oracle/OAS and the fly likes Serial number in SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Serial number in SQL" Watch "Serial number in SQL" New topic
Author

Serial number in SQL

Raghu Devatha
Ranch Hand

Joined: Feb 10, 2009
Posts: 39
Hello,

I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50.

The table may not have all the Sequence number. For example following is the data in my table..

NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3



I need a SQL to get the data like

NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50


Basically I want to have rows for the sequence numbers which are not present i my table.

I thought of using the following query to generate a sequence and then join, but does not help



I tried something like



dR
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3454
    
  47

Raghu Devatha wrote:
I thought of using the following query to generate a sequence and then join, but does not help



That would be my first approach too. What exactly did you try and in which way did it "not help"?
Gene Hilpert
Ranch Hand

Joined: May 22, 2002
Posts: 50

You need an outer join on your table with data in it.
Sorry the style is the old join the (+) is put on the table that is missing the data.




where table looks like
Raghu Devatha
Ranch Hand

Joined: Feb 10, 2009
Posts: 39
Thanks. It worked.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Serial number in SQL
 
Similar Threads
Retrieving a subset of rows with SQL
Oracle Pivot table
Difference between rowid and rownum in sql query??
DB2 700 and 701 - Test Sample
Selecting random rows