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!

Duplicates in the results

Discussion in 'SQL PL/SQL' started by lcd, Jan 22, 2015.

  1. lcd

    lcd Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hello! I don't have much experience in Oracle Pl SQL, but I have a complex query, and its bring me duplicates in the data, I used distinct to avoid that but is still duplicating, can someone bring me some light, I really appreciated it!
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    You NEED to show the query you are using.
     
  3. lcd

    lcd Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    DTSIGuy- its too large to show, let me see if i can get a sample from it
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    If you are performing a SELECT DISTINCT..., the results cannot contain perfect duplicates. That may mean that what appears to be a duplicate is really not. For example one result might have 'Fred' in a column and another 'Fred ' (i.e. 'Fred' with a trailing space). Non-displaying characters will still make the results distinct to Oracle.

    If you have one or more date fields being returned in the query, by default Oracle displays dates as a whole day (i.e. '22-JAN-14') no matter what hour the date contains. Therefore a date which was from January 22nd at 3:00 A.M. and one from 7:32 P.M. would both show in query results as '22-JAN-14'. Because they are distinct values, a query using the DISTINCT operator would show both rows even though the display made them appear to be the same.
     
  5. RajeshIr

    RajeshIr Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    hi all,
    using this query to avoid the duplicates rows
    column_name using constraints column
    Select * from table_name where rowid in (select max(rowid) from table_name group by column_name);
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You aren't taking into consideration that the actual column_name values aren't distinct and may contain extra spaces or unprintable characters. For example I load a table in this manner:


    Code (SQL):

    SQL>  INSERT INTO t_test_usr_data VALUES ('David', '32423423Y', 'Street xxxxx');


    1 ROW created.


    SQL>  INSERT INTO t_test_usr_data VALUES ('David' ,'3422342O', 'Street yyyyy');


    1 ROW created.


    SQL>  INSERT INTO t_test_usr_data VALUES (' David', '324234238', 'Street xxxxx');


    1 ROW created.


    SQL>  INSERT INTO t_test_usr_data VALUES ('David ' ,'34223427', 'Street yyyyy');


    1 ROW created.


    SQL>  INSERT INTO t_test_usr_data VALUES ('John' ,'34223421', 'Street yyyyy');


    1 ROW created.


    SQL>  INSERT INTO t_test_usr_data VALUES ('Maria' ,'34223422', 'Street yyyyy');


    1 ROW created.


    SQL>  INSERT INTO t_test_usr_data VALUES ('Maria' ,'34223423', 'Street yyyyy');


    1 ROW created.


    SQL>  INSERT INTO t_test_usr_data VALUES ('Maria' ,'34223424', 'Street yyyyy');


    1 ROW created.


    SQL>  INSERT INTO t_test_usr_data VALUES ('Maria' ,'34223425', 'Street yyyyy');


    1 ROW created.


    SQL>  commit;


    Commit complete.
     



    Now I use your query to return distinct results:


    Code (SQL):

    SQL> SELECT * FROM t_test_usr_data WHERE rowid IN (SELECT MAX(rowid) FROM t_test_usr_data GROUP BY user_name);


    USER_NAME           OTHER                ADDR
    ------------------- -------------------- -----------------
    David               3422342O             Street yyyyy
     David              324234238            Street xxxxx
    David               34223427             Street yyyyy
    John                34223421             Street yyyyy
    Maria               34223425             Street yyyyy


    SQL>
     

    Each record is distinct, and each USER_NAME is distinct because of the extra spaces (either leading or trailing) in the name. Your query is doing exactly what you want it to do; your data, however, makes it look otherwise.