Need Record Number

[kwil38]

I'm need to have a unique record number for each row that a Select statement returns (i.e. - 1 for the 1st record, 2 for the second record, etc.). I've been attempting to do this via "Count", however I am receiving the same record number (1) for each row returned.

SELECT COUNT(*) "RecNum", company_number, accounting_unit, account_number, description, amount, cr_dr
FROM schema.view
WHERE applied_date = '31-JAN-05'
GROUP BY company_number, accounting_unit, account_number, description, amount, cr_dr;

Am I going about this the wrong way or is there something I'm missing??? Thanks.

 

[SantaMufasa]

Kwil,

If you want a "unique record number" that applies to a row just for the life of that specfic iteration of your query, then you can use the "zero-argument function" named ROWNUM. So here is a variation of your query that illustrates the use of ROWNUM:

SELECT ROWNUM "RecNum", company_number, accounting_unit, account_number, description, amount, cr_dr
FROM schema.view
WHERE applied_date = '31-JAN-05';

The reason I say that ROWNUM "applies to a row just for the life of that specfic iteration," is because if someone INSERTs several more rows "WHERE applied_date = '31-JAN-05' ", then all the value of ROWNUM in a subsequent query would still be unique, that any specific ROWNUM value may appear next to a different row, Got it?

Let us know your findings and satisfaction with this suggestion.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[kwil38]

Thanks for helping me out again Santa - that's just what I am looking to do. Maybe one day I'll have a 1/10th of your Oracle knowledge!