CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
uid
uid

Reputation: 347

How I can display duplicate rows?

How I can display duplicated rows grupe by firstname and lastname but in different rows ?

I have the next code : 

SELECT    LASTname, FIRSTname, COUNT(*) AS dupes  
FROM       TCPD_PERSONEL
GROUP BY FIRSTNAME, LASTNAME, 
HAVING      (COUNT(*) > 1);

when I run this appear : 

lastname   firstname    dupes
Ion          Alin         2

but I want to appear something like this:

lastname  firstname      email 
Ion         Alin        alin@yahoo
Ion         Alin        ion@yahoo

Upvotes: 1

Views: 136

Answers (5)

Mike Clark
Mike Clark

Reputation: 1870

Try this:

 SELECT * FROM tblInfo INNER JOIN (           
 SELECT    firstname, lastname, COUNT(*) AS temp  
 FROM       tblInfo
 GROUP BY lastname, firstname
 HAVING      (COUNT(*) > 1)
 ) x
 ON x. firstname = tblInfo. firstname AND
 x. lastname = tblInfo. lastname;

replace ‘tblInfo’ with ‘TCPD_PERSONEL’

Upvotes: 0

Juan Girini
Juan Girini

Reputation: 1168

You would need to join that query you have made with the same table again:

SELECT * FROM TCPD_PERSONEL 
INNER JOIN
(
SELECT    LASTname, FIRSTname, COUNT(*) AS dupes  
FROM       TCPD_PERSONEL
GROUP BY FIRSTNAME, LASTNAME
HAVING      (COUNT(*) > 1)
) x
ON x.LASTname = TCPD_PERSONEL.LASTname AND
x.FIRSTname = TCPD_PERSONEL.FIRSTname;

Upvotes: 2

Biswabid
Biswabid

Reputation: 1409

Looking at your code I think Distinct is the best option for you.

SELECT  DISTINCT  LASTname, FIRSTname,email 
FROM    TCPD_PERSONEL

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Use analytic functions:

SELECT p.*
FROM (SELECT p.*, COUNT(*) OVER (PARTITION BY FIRSTNAME, LASTNAME) as cnt  
      FROM TCPD_PERSONEL p
     ) p
WHERE cnt >= 2;

EDIT:

If the rows don't have complete duplicates (i.e., the email is different), then the following is perhaps the most efficient method:

select p.*
from TCPD_PERSONEL p
where exists (select 1
              from TCPD_PERSONEL p2
              where p2.firstname = p.firstname and p2.lastname = p.lastname and
                    p2.email <> p.email
             );

For performance, you want an index on TCPD_PERSONEL(lastname, firstname, email).

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Try this too

SELECT t1.LASTname,t1.FIRSTname,t1.email from TCPD_PERSONEL t1 inner join
(
SELECT    LASTname, FIRSTname, COUNT(*) AS dupes  
FROM       TCPD_PERSONEL
GROUP BY FIRSTNAME, LASTNAME, 
HAVING      (COUNT(*) > 1)
) t2 on t1.LASTname=t2.LASTname and t1.FIRSTname=t2.FIRSTname;

Upvotes: 6

Related Questions