Row Numbers in result set
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Row Numbers in result set

  1. #1
    Join Date
    Jun 2003
    Location
    India
    Posts
    4

    Row Numbers in result set

    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.
    Last edited by nellaiarun; 06-27-2003 at 02:21 AM.

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    Code:
    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.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Jun 2003
    Location
    India
    Posts
    4
    Hi ales,

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

    - Arun.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width