Row Numbers in result set

[nellaiarun]

Hi Folks,

Consider the following data in my table.
<PRE>
-------------------
[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
..........
----------------------------
</PRE>
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.