Re: Count child of every node in hier table reading source just once

On Tue, 18 Mar 2008 21:20:26 +0100, Maxim Demenko <mdemenko_at_gmail.com> wrote:

>Mauro Pagano schrieb:
>> Hi guys,
>> I'm trying to write a SQL statement to count how many child every node
>> in a hierarchical table has.
>> I want to perform this operation reading source table only one time.
>>
>> Following an example of source table:
>>
>> CREATE TABLE TEST_HIER
>> (
>> ID NUMBER,
>> PID NUMBER
>> );
>>
>> Insert into TEST_HIER
>> (ID, PID)
>> Values
>> (1, 0);
>> Insert into TEST_HIER
>> (ID, PID)
>> Values
>> (2, 1);
>> Insert into TEST_HIER
>> (ID, PID)
>> Values
>> (3, 1);
>> Insert into TEST_HIER
>> (ID, PID)
>> Values
>> (4, 2);
>> Insert into TEST_HIER
>> (ID, PID)
>> Values
>> (5, 3);
>> COMMIT;
>>
>> And expected result is:
>>
>> PID CHILD
>> ---------- ----------
>> 0 1
>> 1 2
>> 2 1
>> 3 1
>>
>> Any idea?
>> Thanks a lot
>> Mauro
>
>As you are counting only direct children, the number of children is
>exactly the same, as every parent is referenced. So, you don't need
>connect by at all, simple
>
>select pid,count(*)
>from test_hier t
>group by pid
>
>gives you expected output.

I didn't catch he only wanted direct children, and if OP claims his question is a "joke", i won't waste my time on him next time. .......
We run Oracle 9iR2,10gR2, 10g2RAC on RH4/RH5 and Solaris 10 (Sparc) remove NSPAM to email Received on Tue Mar 18 2008 - 23:10:29 CDT