Question

Toad for Oracle & DevOps Toolkit
1

Could someone help me to compose select statement for the following logic?

I have to load the table from DW staging to my database. The original table does not have primary key, and it has detail records for other, control table (as foreign key). Basically, for one ID it has multiple records.
I would like to number the records within ID group (will be a new column in SQL statement - DETAIL_RECORD_NUMBER).
Example,

ID DETAIL_RECORD_NUMBER

2

This is an SQL question and should be addressed to TOADSQL list, not the TOAD list.

3

Morning Bella,

try this:

SELECT id, row_number() OVER (PARTITION BY id ORDER BY id) AS
DETAIL_RECORD_NUMBER
FROM norman;

Which gives this:

ID DETAIL_RECORD_NUMBER

4

You can use the following SQL to get the serial number per ID.

Select ID

, RANK() OVER ( PARTITION BY ID ORDER BY ID, ROWID ) DETAIL_RECORD_NUMBER

From your_staging_tablename

On Mon, Apr 12, 2010 at 7:35 PM, Bella Bechutsky wrote:

Could someone help me to compose select statement for the following logic?

I have to load the table from DW staging to my database. The original table
does not have primary key, and it has detail records for other, control
table (as foreign key). Basically, for one ID it has multiple records.
I would like to number the records within ID group (will be a new column in
SQL statement - DETAIL_RECORD_NUMBER).
Example,

ID DETAIL_RECORD_NUMBER

5

I tried something like

SELECT id, SUM(1) OVER(PARTITION by id ORDER by rownum) x FROM tab1

The analytical functions are a wonderful, powerful and very efficient Oracle
extension to SQL. We couldn’t do our stuff without them. The alternative
is PL/SQL code and of course that may look simpler but it takes much longer to
execute.