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

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 19 Mar 2008 20:42:41 +0100
Message-ID: <47E16CB1.4010502@gmail.com>

NetComrade schrieb:

> 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

He didn't state it, however it is the only conclusion i can make, comparing his input data and expected output.

Best regards

Maxim Received on Wed Mar 19 2008 - 14:42:41 CDT