tip query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: tip query

  1. #1
    Join Date
    Jul 2005
    Posts
    3

    tip query

    Hi all

    When I execute the query:

    SELECT company_parent_id, count(*)
    FROM company
    GROUP BY company_parent_id;

    The result is:


    Code:
    COMPANY_PARENT_ID       COUNT(*)
    -------------------- ----------
    1                           303
    12                            1
    16                            3
    21                            1
    3                             1
    3343                          2
    3350                          1
    3376                          1
    3379                          1
    3381                          1
    3383                          1
    3384                          1
    3396                          3
    3397                          2
    3409                          2
    4                             7
    9                            19
    I need generate a result like following, for each company parent:

    Code:
    COMPANY_PARENT_ID    GEN
    -------------------- ----------
    16					 		  1
    16							  2
    16							  3
    
    3343						  1
    3343						  2
    
    4					 		  1
    4							  2
    4							  3
    4							  4
    4							  5
    4							  6
    4							  7
    Who can I do this???

    thanks!

    []īs

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    So you want to repeat the company_parent_id according to the number of occurances in the company table?

    How about ...
    Code:
    select company_parent_id
    from   company
    order by 1
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    Dave:

    You should have scrolled right on the sample, there was a second column. How about:

    Code:
    SELECT company_parent_id,
           ROW_NUMBER() OVER (PARTITION BY company_parent_id
                              ORDER BY company_parent_id) gen
    FROM company
    John

  4. #4
    Join Date
    Jul 2005
    Posts
    3
    Hi,

    this is that I need.
    I was trying to use rank or dense_rank, just for test, but this
    last example is perfect.

    thank you very much!

    []īs

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