1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to retrieve col1 based on 2nd col

Discussion in 'SQL PL/SQL' started by robin, Mar 1, 2013.

  1. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Hi

    I have the following values in a table ex;

    name no
    -----------
    a 3
    b 2
    c 1

    I want the output as below :

    a
    a
    a
    b
    b
    c

    the column name should repeat as many times as the value in no field for that particular name.

    Note : Please use only SQL Query for solving this(donot use pl/sql).This was one of the interview questions asked in a company.

    Thanks in Advance,
    Robin
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Robin,

    Here is the query to get the output as required.

    Code (Text):

    SQL>SELECT NAME,NO FROM TEST_MULTIPLE
            GROUP BY LEVEL,NAME,NO
                CONNECT BY LEVEL <= NO
                ORDER BY NAME;

    Output:
    -------
    name  no
     a     3
     a     3
     a     3
     b     2
     b     2
     c     1
     
     
    robin and sambuduk like this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Unfortunately this example poses a problem as NO is a reserved word in Oracle:

    Code (SQL):
    SQL> SELECT keyword
      2  FROM v$reserved_words
      3  WHERE keyword LIKE 'NO%'
      4  AND LENGTH(keyword) < 5;
     
    KEYWORD
    ------------------------------
    NOT
    NONE
    NO
     
    SQL>
    I realize this was an interview question but, in my mind, the interviewer should have known the reserved words in Oracle and avoided them as column names. A valid substitute for NO would be NUM.
     
    Bharat likes this.
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi David,

    Yeah we can see that "NO" keyword in reserved words. So what I did was I have used lower cases to create the table column as "no". Is there any problem with this too ? Apologies, Unfortunately I have typed in upper cases in select query in my previous post.
     
  5. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    The suggested solution by taking into account David's important remark about not using key words seems to me interesting. Yet there is something that I don't understand. I often read Hierarchical Queries in oracle online documentation to understand these queries which turn out to be highly handy and important.

    However for most of the examples provided in the documentation, oracle builds actually a single tree by using START WITH and specifying a root. But in the case of the OP (please correct me if I'm wrong) we're constructing several trees/hierarchies (one per name) what I don't understand is how the GROUP BY LEVEL in the suggested solution separates these trees. For example
    If I run the following
    Code (Text):

    WITH tmptab AS
    (
        SELECT 'c' AS name, 3 as occNum FROM DUAL UNION
        SELECT 'b' AS name, 2 as occNum FROM DUAL UNION
        SELECT 'a' AS name, 3 as occNum FROM DUAL
    )
    SELECT LEVEL, name
    FROM tmptab t1
    CONNECT BY LEVEL <= occNum
    GROUP BY LEVEL, name;
     
    The output will be
    Code (Text):

         LEVEL N
    ---------- -
         3 c
         3 a
         1 a
         1 c
         1 b
         2 b
         2 c
         2 a

    8 rows selected.

    SQL>
     
    Which is the desired output according to the OP's specification, yet if we remove the GROUP BY clause, that is, running the following query instead
    Code (Text):

    WITH tmptab AS
    (
        SELECT 'c' AS name, 3 as occNum FROM DUAL UNION
        SELECT 'b' AS name, 2 as occNum FROM DUAL UNION
        SELECT 'a' AS name, 3 as occNum FROM DUAL
    )
    SELECT LEVEL, name
    FROM tmptab t1
    CONNECT BY LEVEL <= occNum;
     
    The output will include 30 rows!
    Code (Text):

    LEVEL N
    ---------- -
         1 a
         2 a
         3 a
         3 c
         2 b
         3 a
         3 c
         2 c
         3 a
         3 c
         1 b
         2 a
         3 a
         3 c
         2 b
         3 a
         3 c
         2 c
         3 a
         3 c
         1 c
         2 a
         3 a
         3 c
         2 b
         3 a
         3 c
         2 c
         3 a
         3 c

    30 rows selected.

    SQL>
     
    Which is obviously not what we're looking for.

    So, I think I'm not able to understand the real impact of the GROUP BY LEVEL on how levels are being built and separated for each tree

    Could someone kindly make some clarification?

    Thanks in advance,
    Dariyoosh
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> SELECT name, num, level
      2  FROM repeat_col
      3  CONNECT BY level <= num
      4  ORDER BY name,level;
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          1
    a           3          1
    a           3          1
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          2
    a           3          3
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    a           3          3
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          3
    b           2          1
    b           2          1
    b           2          2
    b           2          2
    b           2          2
    b           2          2
    b           2          2
    b           2          2
    b           2          2
    b           2          2
    NA        NUM      LEVEL
    -- ---------- ----------
    b           2          2
    b           2          2
    b           2          2
    b           2          2
    c           1          1
     
    126 ROWS selected.
     
    SQL>
    SQL> SELECT name, num, level
      2  FROM repeat_col
      3  GROUP BY level, name, num
      4  CONNECT BY level <= num
      5  ORDER BY name;
     
    NA        NUM      LEVEL
    -- ---------- ----------
    a           3          1
    a           3          2
    a           3          3
    b           2          1
    b           2          2
    c           1          1
     
    6 ROWS selected.
     
    SQL>
    SQL> SELECT name, num
      2  FROM repeat_col
      3  GROUP BY level, name, num
      4  CONNECT BY level <= num
      5  ORDER BY name;
     
    NA        NUM
    -- ----------
    a           3
    a           3
    a           3
    b           2
    b           2
    c           1
     
    6 ROWS selected.

    SQL>
    The group by level reduces the output to one row per level per name.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Only the issue of having to always use quotation marks to access the column, which is another discussion entirely. You have a 'workaround' but it's not a good one in my estimation.
     
  8. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hello David,

    Thank you very much for your help. As the example you provided indicates the use of GROUP BY eliminates duplicates and as you said allows to reduce the output to one row per level per name.

    What I wanted to ask (and I think maybe I didn't express my question properly in my precedent comment) is that without GROUP BY a lot of rows are generated for each name. As your example shows the number of rows are far bigger than the "num" for each name. For example, if you take a look at the SQL*Plus output you provided in your last comment, the letter 'a' (name) is to be repeated 3 times (because num = 3 when name= 'a'). When you remove the GROUP BY clause, that if we just write

    Code (SQL):

    SELECT name, num, level
    FROM repeat_col
    CONNECT BY level <= num
    ORDER BY name,level;
     
    This generates for name='a', 111 rows whereas num = 3.
    Therefore, what I don't understand is, how CONNECT BY generates 111 rows
    while the num for 'a' is only 3, what's the link? How the hierarchy is built that gives
    too much rows?

    Likewise for name='b' we have num = 2 whereas the same query (without GROUP BY)
    gives 14 rows for 'b'.

    So why CONNECT BY generates these extra rows? The number of generated rows are
    based on what exactly (because as I see that it is not really related to num)?

    Thanks in advance,

    Regards,
    Dariyoosh
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The connect by is generating results according to the following formula:

    level*(num^level)

    for levels < 3 which translates to the level times the number to the power of level. Let's put in some numbers and see what happens:

    NUM=3, LEVEL=1

    1*(3^1) == 1*3 == 3 <--- So far, so good, as we get 3 results for a at level 1

    NUM=3, LEVEL=2

    2*(3^2) == 2 * 19 == 18 ,--- Again, results match our observations as 18 rows are generated for a at level 2

    For levels >= 3 a dfferent forumla appears:

    (level^(level - 1))+ (level*(num^level))

    For NUM=3, LEVEL=3

    (3^2)+(3*(3^3)) == 9 + (3^4) == 9 + 81 == 90 <--- Agrees with observation as we do get 90 rows for a at level 3

    Connect by keeps connecting untl it can't connect anymore, producing some unexpected results depending on the connect by condition. The group by, as explained before, limits the output to one row per level per number.
     
    robin, dariyoosh and Bharat like this.
  10. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Thanks a lot for this explanation David.

    Regards,
    Dariyoosh
     
  11. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Thanks a lot for the explanation guys ! Really Helped a lot.


    Regards
    Robin