CODEWITHSUNDEEP
sqloracle-database
HelloFriends
HelloFriends

Reputation: 25

How do I remove duplicate records

Query: 

Select table_c.id_number, table_c.name, table_s.site_name,table_co.Contract_name 
FROM table_c , table_s, table_m, table_o, table_a, table_con 
  WHERE 
   table_s.objid = table_c.sobjid
   AND table_m.cobjid (+) = table_c.objid 
   AND table_o.objid (+) = table_m.olobjid   
   AND table_a.objid (+) = table_o.aobjid 
   AND table_co.objid (+) = table_a.conobjid;

Here I have 6 tables. table_c and table_s have one 2 one relationship. It is possible that for 1 table_o record, we can have 2 table_c records or may be no record.Since I need to take table_co values in SELECT along with table_c and table_s tables, I used left out join on all tables table_c->table_m->table_o->table_a->table_co.

Now on running this query it gives me duplicate records. I have millions of records in table_c so if i use distinct or Union ALL to remove duplicate records, my query takes very long time and this is not acceptable solution.

Can I right this query in such a way that it gives me unique records without performance issue.

Please note, this query is part of a sql view is used by external systems to get data.

Thanks in advance.

Upvotes: 0

Views: 157

Answers (1)

sagi
sagi

Reputation: 40491

Try using ROW_NUMBER() :

SELECT * FROM (
    Select table_c.id_number, table_c.name, table_s.site_name,table_co.Contract_name ,
           ROW_NUMBER() OVER(PARTITION BY table_c.id_number,table_c.name ORDER BY 1) as rnk
    FROM table_c 
    INNER JOIN table_s ON(table_s.objid = table_c.sobjid)
    LEFT OUTER JOIN table_m ON(table_m.cobjid = table_c.objid )
    LEFT OUTER JOIN table_o ON(table_o.objid = table_m.olobjid)
    LEFT OUTER JOIN table_a ON(table_a.objid = table_o.aobjid )
    LEFT OUTER JOIN table_con ON(table_co.objid = table_a.conobjid))
WHERE rnk = 1;

Notes: Please avoid the use of implicit join syntax(comma separated) and use the proper syntax of a join.

I used PARTITION BY table_c.id_number,table_c.name , add all the columns specify a 'unique' row.

Upvotes: 1

Related Questions