Serial number in SQL
-
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
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num -
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
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =numI guess you're using Oracle, since you select from '
dual'. You may: 1. Declare a temporary table:CREATE GLOBAL TEMPORARY TABLE temp_number
( number_column NUMBER( 10, 0 )
)
ON COMMIT DELETE ROWS;2. Fill it with numbers from 1 to 50.
INSERT INTO temp_number
SELECT rownum FROM ALL_OBJECTS
WHERE rownum BETWEEN 1 AND 50You may prefer a
forloop. 3. Outer-join it to your table.SELECT number_column, NAME
FROM TEMP_NUMBER
LEFT OUTER JOIN ABC
ON TEMP_NUMBER.number_column = ABC.SEQUENCE
ORDER BY number_columnHope this helps, Pablo.
Pablo. "Accident: An inevitable occurrence due to the action of immutable natural laws." (Ambrose Bierce, circa 1899).
-
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
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =numThis is not Oracle but may give you some useful pointers...Using User Defined Functions in Table Inserts and Seeding with Stored Procedures[^] Might be worth a look.
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
-
This is not Oracle but may give you some useful pointers...Using User Defined Functions in Table Inserts and Seeding with Stored Procedures[^] Might be worth a look.
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife