File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Serial number in SQL

 
Raghu Devatha
Ranch Hand
Posts: 39
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
Martin Vajsar
Sheriff
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51
Eclipse IDE Hibernate Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 39
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks. It worked.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic