Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate emails, but need to isolate column value
Duplicate emails, but need to isolate column value [message #185824] Thu, 03 August 2006 15:15 Go to next message
blakhama
Messages: 10
Registered: August 2006
Junior Member
I am trying to only isolate duplicate emails, but with different QUERY6(billing address) results

Here is what I use to get duplicate emails
SELECT QUERY1, QUERY5, QUERY6 FROM CSUSERHISTORY
WHERE (QUERY1) IN (SELECT QUERY1 FROM CSUSERHISTORY HAVING COUNT(QUERY1) > 1 GROUP BY QUERY1 )
AND QUERY1 NOT LIKE '%@XX.COM' ORDER BY QUERY1

results
QUERY1 QUERY5 QUERY6
joe@this.com 11111 333333
joe@this.com 11111 333333
joe@this.com 11111 444444
mike@there.com 12222 555555
mike@there.com 12222 555555

I want to isolate the duplicate record that has only QUERY6 different ie. (joe@this.com 11111 444444). THe records must query duplicate emails.

Sorry for not being clear enough. Having a difficult time explaining and figuring this out. Mad
Re: Duplicate emails, but need to isolate column value [message #185856 is a reply to message #185824] Thu, 03 August 2006 18:01 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Yeah, your requirement is a bit nebulous. Are you saying that, for a given query1 value, you only want the query6 values that appear once and there is at least one other query6 value that appears multiple times?

sql>select t.*, count(*) over (partition by query1, query6) cnt
  2            from t;

QUERY1          QUERY5          QUERY6                CNT
--------------- --------------- --------------- ---------
joe@this.com    11111           333333                  2
joe@this.com    11111           333333                  2
joe@this.com    11111           444444                  1
mike@there.com  12222           555555                  2
mike@there.com  12222           555555                  2

5 rows selected.

sql>select *
  2    from (select t.*, count(*) over (partition by query1, query6) cnt
  3            from t) t1
  4   where cnt = 1
  5     and exists (select null
  6                   from t t2
  7                  where t2.query1 = t1.query1
  8                    and t2.query6 <> t1.query6
  9                  group by t2.query1, t2.query6
 10                  having count(*) > 1);

QUERY1          QUERY5          QUERY6                CNT
--------------- --------------- --------------- ---------
joe@this.com    11111           444444                  1

1 row selected.
icon14.gif  Re: Duplicate emails, but need to isolate column value [message #185859 is a reply to message #185824] Thu, 03 August 2006 20:36 Go to previous message
blakhama
Messages: 10
Registered: August 2006
Junior Member
Todd, awesome, works perfect! This query was used to reference a table containing over 4 million records! Cool

I am still stumped on explaining this query, I don't even know how it was explained to me, but the result is exactly what was needed, thanks!!!

[Updated on: Thu, 03 August 2006 20:37]

Report message to a moderator

Previous Topic: how to add hours to date
Next Topic: Specifically inserting a Row into a Result (+_+)
Goto Forum:
  


Current Time: Sun Dec 11 08:29:15 CST 2016

Total time taken to generate the page: 0.10701 seconds