Row Numbers in result set

[nellaiarun]

Hi Folks,

Consider the following data in my table.

-------------------

[CustID] | [Amount]

-------------------

  ABC        50

  ABC        500

  ABC        100



  EFG        100

  EFG        200

  EFG        300



  IJK        200

  IJK        200

--------------------



Now, I want the following as o/p.



-------------------

[CustID] | [Amount]

-------------------

  ABC        650

  EFG        600

  IJK        400

--------------------



This is doable using the following query.



Select CustID, Sum(Amount) From MyTable Group by CustId



But, I want some extra information like the following.



----------------------------

[CustID] | [Amount] | ROWID

----------------------------

  ABC         650       1

  EFG         600       2

  IJK         400       3

..........

----------------------------

Meaning I just want to have the row number - in the result set. The Row Number value should be a sequential no, starting from 1 to no.of records in the result set. PLEASE REMEMBER: This sequential no. should be generated after applying the "group by" function. So Obviously we must use that ROWID column in the "Group by" Clause, since it's not an aggregated field. But I want the row numbers to be in sequential order. I am in a damn need of help. Is this possible? What should I use in my query. Any help?

Thanks,
- Arun.

[ales]

Hi,

select CustID, Amount, rownum as row_id from (Select CustID, Sum(Amount) as Amount From MyTable Group by CustId)

Do not confuse rownum and rowid. Rowid has special meaning in Oracle.

[nellaiarun]

Hi ales,

Thanks for your time & effort. Your code works well. Thank U once again.

- Arun.