tip query

[Tads]

Hi all :D

When I execute the query:

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

The result is:



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:


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 :)

[slimdave]

So you want to repeat the company_parent_id according to the number of occurances in the company table?

How about ...

select company_parent_id
from company
order by 1

[John Spencer]

Dave:

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

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

[Tads]

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