4 Replies - 746 Views - Last Post: 18 August 2017 - 10:39 AM Rate Topic: -----

#1 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

how to remove duplicates?

Posted 17 August 2017 - 01:58 PM

I am using oracle

how to remove duplicates?

Select DISTINCT 
t1.my_key,  
LISTAGG(t3.name, ' & ') WITHIN group (order by t1.my_key) as name,
LISTAGG(t31.name, ' & ') WITHIN group (order by t1.my_key) as name2,
t2.description,
t1.test_date, 
t4.reason
From Table1 t1
left join (Select DISTINCT Et_Key, my_key, db_Key, description from Table2) t2 on t2.my_key = t1.my_key
left join Table3 t3 on me.Et_Key = t3.Et_Key
left join Table4 t4 on t1.my_key = t4.my_key
left join Table5 t5 on t1.my_key = t5.my_key 
left join Table6 t6 on t6.my_key = t1.my_key
left join Table3 t31 on t31.Et_Key = t6.Et_Key
group by t1.my_key, t2.description,t1.test_date, 




-------
output:
|-------------------------|-------------|-------------|-----------|
|my_key| name             | name2       | description | test_date |
|------|------------------|-------------|-------------|-----------|
| 111  | bob & bob        | dave & james| random desc | 1/1/2015  | 
| 112  | bob & test       | dave & james| random desc | 1/1/2015  | 
| 133  | jam & jam & jam  | dave & james| random desc | 1/1/2015  | 
--------------------------------------------------------------------




row 111 is wrong bc there are 2 duplicate bob's
row 112 is correct bc col name & name2 are not duplicates
row 133 is wrong bc there are 3 duplicate jam's

This post has been edited by ikhlas06: 17 August 2017 - 02:00 PM


Is This A Good Question/Topic? 0
  • +

Replies To: how to remove duplicates?

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6979
  • View blog
  • Posts: 14,599
  • Joined: 16-October 07

Re: how to remove duplicates?

Posted 18 August 2017 - 06:31 AM

You know, I've been an Oracle DBA for nearly two decades and I've never used LISTAGG...

Ok, looking at t3, you're pretty much only using it for that column? With this in mind, clean the data in the join. Which, curiously, is exactly what you did on t2.
left join (select distinct Et_Key, name from Table3) t3 on me.Et_Key = t3.Et_Key



While distinct makes sense in this context, don't lean on it for that main select. It can easily hide logic errors.

Also, start thinking about more descriptive table names, because damn.

Hope this helps.
Was This Post Helpful? 1
  • +
  • -

#3 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

Re: how to remove duplicates?

Posted 18 August 2017 - 07:47 AM

View Postbaavgai, on 18 August 2017 - 06:31 AM, said:

You know, I've been an Oracle DBA for nearly two decades and I've never used LISTAGG...

Ok, looking at t3, you're pretty much only using it for that column? With this in mind, clean the data in the join. Which, curiously, is exactly what you did on t2.
left join (select distinct Et_Key, name from Table3) t3 on me.Et_Key = t3.Et_Key



While distinct makes sense in this context, don't lean on it for that main select. It can easily hide logic errors.

Also, start thinking about more descriptive table names, because damn.

Hope this helps.


thank you, good idea but I did try this but no luck


This is the output I want. I was think maybe there is a oracle function, which allows me to find duplicates in col and replace them them ''?

-------
output:
|-------------------------|-------------|-------------|-----------|
|my_key| name             | name2       | description | test_date |
|------|------------------|-------------|-------------|-----------|
| 111  | bob              | dave & james| random desc | 1/1/2015  | 
| 112  | bob & test       | dave & james| random desc | 1/1/2015  | 
| 133  | jam              | dave & james| random desc | 1/1/2015  | 
--------------------------------------------------------------------



This post has been edited by ikhlas06: 18 August 2017 - 07:56 AM

Was This Post Helpful? 0
  • +
  • -

#4 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

Re: how to remove duplicates?

Posted 18 August 2017 - 08:07 AM

maybe replace function?

replace(LISTAGG(t3.name, ' & ') WITHIN group (order by t1.my_key) as name, '', '') as name,


but I am not sure what to put in parmeters
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6979
  • View blog
  • Posts: 14,599
  • Joined: 16-October 07

Re: how to remove duplicates?

Posted 18 August 2017 - 10:39 AM

View Postikhlas06, on 18 August 2017 - 09:47 AM, said:

maybe there is a oracle function, which allows me to find duplicates in col and replace them them ''?


Oracle, or any SQL database, tend to be light on string manipulation functions. Mostly because databases manipulate data already in tables.

My suggestion was just a guess, as there's only so much to be done without actual data or structure. If duplication isn't from table entries then it's being generated by some join logic.

The way I usually start with these things it to just get all data and whittle it down. Maybe start with:
Select t1.my_key, t3.name, t31.name as name2, t2.description, t1.test_date, t4.reason
  From Table1 t1
    left join (Select DISTINCT Et_Key, my_key, db_Key, description from Table2) t2 on t2.my_key = t1.my_key
    left join Table3 t3 on me.Et_Key = t3.Et_Key
    left join Table4 t4 on t1.my_key = t4.my_key
    left join Table5 t5 on t1.my_key = t5.my_key 
    left join Table6 t6 on t6.my_key = t1.my_key
    left join Table3 t31 on t31.Et_Key = t6.Et_Key



Hmm... looking closer at this. There is not "me" table here.

Let's focus on t31. We could express this singly like so:
Select t6.my_key, LISTAGG(t31.name, ' & ') WITHIN group (order by t6.my_key) as name
  from Table6 a
    join Table3 t31 on t31.Et_Key = t6.Et_Key



Or, we can express it like this:
select a.my_key, LISTAGG(a.name, ' & ') WITHIN group (order by a.my_key) as name
  from (
    Select t6.my_key, t31.name
      from Table6 a
        join Table3 t31 on t31.Et_Key = t6.Et_Key
    ) b



Now, at that level, if we needed to remove dups, we could simply do this:
select a.my_key, LISTAGG(a.name, ' & ') WITHIN group (order by a.my_key) as name
  from (
    Select distinct t6.my_key, t31.name
      from Table6 a
        join Table3 t31 on t31.Et_Key = t6.Et_Key
    ) b



Again, with meaningless names and no data or structure, there's only so far one can go.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1