Home » SQL & PL/SQL » SQL & PL/SQL » parent and Child issue ?
parent and Child issue ? [message #621442] Wed, 13 August 2014 06:43 Go to next message
tondapi
Messages: 98
Registered: August 2007
Location: usa
Member
In my table I have parent_id and child_id for each parent 100 + child_id are there. I need to display only 7 child_id for each parent.
Please help me
Re: parent and Child issue ? [message #621443 is a reply to message #621442] Wed, 13 August 2014 06:51 Go to previous messageGo to next message
Littlefoot
Messages: 19619
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A little bit of analytics could help. Here's an example:

SQL> WITH seven_of_nine
  2          AS (SELECT d.deptno,
  3                     d.dname,
  4                     e.ename,
  5                     e.job,
  6                     e.sal,
  7                     ROW_NUMBER () OVER (PARTITION BY d.deptno ORDER BY e.sal)
  8                        rn
  9                FROM dept d, emp e
 10               WHERE e.deptno = d.deptno)
 11    SELECT s.deptno,
 12           s.dname,
 13           s.ename,
 14           s.job,
 15           s.sal
 16      FROM seven_of_nine s
 17     WHERE s.rn <= 3
 18  ORDER BY s.deptno, s.rn;

    DEPTNO DNAME          ENAME      JOB              SAL
---------- -------------- ---------- --------- ----------
        10 ACCOUNTING     MILLER     CLERK           2100
        10 ACCOUNTING     CLARK      MANAGER         3250
        10 ACCOUNTING     KING       PRESIDENT       5800
        20 RESEARCH       SMITH      CLERK           1600
        20 RESEARCH       ADAMS      CLERK           1900
        20 RESEARCH       JONES      MANAGER         3775
        30 SALES          JAMES      CLERK           1750
        30 SALES          WARD       SALESMAN        2050
        30 SALES          MARTIN     SALESMAN        2050

9 rows selected.

SQL>

[Updated on: Wed, 13 August 2014 06:52]

Report message to a moderator

Re: parent and Child issue ? [message #621451 is a reply to message #621442] Wed, 13 August 2014 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I need to display only 7 child_id for each parent.


Do you mean 7 of the direct children (and which ones) or up to 7 levels?

Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: parent and Child issue ? [message #621452 is a reply to message #621443] Wed, 13 August 2014 08:00 Go to previous message
joy_division
Messages: 4514
Registered: February 2005
Location: East Coast USA
Senior Member
Littlefoot, you should not have fed the troll. You are probably going to be told your example only shows 3 for each department and it wants 7.
Take a look at previous questions by this poster.
Previous Topic: Filling the Rows of a PL/SQL Nested Table
Next Topic: SQL
Goto Forum:
  


Current Time: Fri Sep 19 07:11:46 CDT 2014

Total time taken to generate the page: 0.24171 seconds