Reputation: 347
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
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
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
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
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
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