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!

Distinct Clause is not Working

Discussion in 'General' started by Shruthic, Aug 2, 2013.

  1. Shruthic

    Shruthic Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have the following issue with the query given below,
    My Table is in the following format:
    Table 1
    ColA ||ColB
    1 ||AA
    1 ||AA
    2 ||BB
    3 ||CC

    Table 2
    ColA ||ColC
    1 ||X
    2 ||Y
    3 ||Z

    For the query ,
    select distinct colB,ColC
    from Table1, Table2 where Table1.ColA=Table2.ColA

    I want my result in the format
    ColB ||ColC
    AA ||X
    BB ||Y
    CC ||Z

    But, I am getting the following result
    ColB ||ColC
    AA ||X
    AA ||X
    BB ||Y
    CC ||Z
    ie., distinct is not working in this case. Can some one please provide a solution for this issue
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,

    Try with the following query.

    SELECT colB,ColC
    FROM (SELECT DISTINCT colA,colB FROM Table1) t1
    , Table2
    WHERE T1.ColA=Table2.ColA




    Regards
    sambasiva Reddy.K
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your query IS working -- you need to check Table1 for the 'AA' values as I suspect one of them has a trailing space to it, making it 'AA '.

    When the 'AA' data matches I get this:

    Code (SQL):
    SQL> CREATE TABLE table1(
      2          cola NUMBER,
      3          colb    varchar2(4));
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE table2(
      2          cola    NUMBER,
      3          colc    varchar2(4));
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO table1
      3  VALUES(1, 'AA')
      4  INTO table1
      5  VALUES(1, 'AA')
      6  INTO table1
      7  VALUES(2, 'BB')
      8  INTO table1
      9  VALUES(3, 'CC')
     10  INTO table2
     11  VALUES(1, 'X')
     12  INTO table2
     13  VALUES(2, 'Y')
     14  INTO table2
     15  VALUES(3, 'Z')
     16  SELECT * FROM dual;
     
    7 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT DISTINCT colb, colc
      2  FROM table1, table2 WHERE table2.cola = table1.cola;
     
    COLB COLC
    ---- ----
    BB   Y
    AA   X
    CC   Z
     
    SQL>
     
    When I add another 'AA' record but add a space to the end I get this:

    Code (SQL):
    SQL> INSERT INTO table1
      2  VALUES(1, 'AA ');
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT DISTINCT colb, colc
      2  FROM table1, table2 WHERE table2.cola = table1.cola;
     
    COLB COLC
    ---- ----
    BB   Y
    AA   X
    AA   X
    CC   Z
     
    SQL>
    Looking at the output to show the distinct colb values:

    Code (SQL):
    SQL> SELECT DISTINCT ''''||colb||'''', colc
      2  FROM table1, table2 WHERE table2.cola = table1.cola;
     
    ''''|| COLC
    ------ ----
    'AA '  X
    'BB'   Y
    'CC'   Z
    'AA'   X
     
    SQL>
    Notice that it DOES return distinct values.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your query won't work any better if the data being returned is distinct:

    Code (SQL):
    SQL> SELECT colB,ColC
      2  FROM (SELECT DISTINCT colA,colB FROM Table1) t1
      3  , Table2
      4  WHERE T1.ColA=Table2.ColA
      5  /
     
    COLB COLC
    ---- ----
    AA   X
    AA   X
    BB   Y
    CC   Z
     
    SQL>
    In this case both 'AA' and 'AA ' exist in Table1; the results returned are, therefore, distinct.