• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

Serial number in SQL

 
Ranch Hand
Posts: 40
  • 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

 
Sheriff
Posts: 3752
62
Netbeans IDE Oracle Chrome
  • 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"?
 
Ranch Hand
Posts: 51
Hibernate Eclipse IDE Oracle
  • 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: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks. It worked.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!