CODEWITHSUNDEEP
sqlsql-server-2008
Hiren
Hiren

Reputation: 1391

Show all rows that have certain columns duplicated

suppose I have following sql table

    objid  firstname lastname active
     1       test      test     0
     2       test      test     1
     3       test1     test1    1
     4       test2     test2    0
     5       test2     test2    0
     6       test3     test3    1

Now, the result I am interested in is as follows:

     objid  firstname lastname active
     1       test      test     0
     2       test      test     1
     4       test2     test2    0
     5       test2     test2    0

How can I achieve this? I have tried the following query,

select firstname,lastname from table
group by firstname,lastname
having count(*) > 1

But this query gives results like 

    firstname  lastname
     test        test
     test2       test2

Upvotes: 35

Views: 101681

Answers (11)

Md. Nazmul Alom
Md. Nazmul Alom

Reputation: 199

Please try

WITH cteTemp AS (
  SELECT EmployeeID, JoinDT,
     row_number() OVER(PARTITION BY EmployeeID, JoinDT ORDER BY EmployeeID) AS [RowFound]
  FROM dbo.Employee 
)
SELECT * FROM cteTemp WHERE [RowFound] > 1 ORDER BY JoinDT

Upvotes: 0

scientific_explorer
scientific_explorer

Reputation: 915

I'm surprised that there is no answer using Window function. I just came across this use case and this helped me.

select t.objid, t.firstname, t.lastname, t.active
from
(
select t.*, count(*) over (partition by firstname, lastname) as cnt
from my_table t
) t
where t.cnt > 1;

Fiddle - https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c0cc3b679df63c4d7d632cbb83a9ef13

The format goes like

select
    tbl.relevantColumns
from
(
    select t.*, count(*) over (partition by key_columns) as cnt
    from desiredTable t
) as tbl
where tbl.cnt > 1;

This format selects whatever columns you require from the table (sometimes all columns) where the count > 1 for the key_columns being used to identify the duplicate rows. key_columns can be any number of columns.

Upvotes: 1

Mahesh Bharati
Mahesh Bharati

Reputation: 61

SELECT user_name,email_ID 
FROM User_Master WHERE 
email_ID 
in (SELECT email_ID 
FROM User_Master GROUP BY 
email_ID HAVING COUNT(*)>1)

enter image description here

Upvotes: 6

pooja
pooja

Reputation: 11

If you want to print all duplicate IDs from the table:

select * from table where id in (select id from table group By id having count(id)>1)

Upvotes: 1

Riccardo
Riccardo

Reputation: 11

This is the easiest way:

SELECT * FROM yourtable a WHERE EXISTS (SELECT * FROM yourtable b WHERE a.firstname = b.firstname AND a.secondname = b.secondname AND a.objid <> b.objid)

Upvotes: 1

Mohammed Safeer
Mohammed Safeer

Reputation: 21545

This Query returns dupliacates

SELECT * FROM (
  SELECT  a.* 
    FROM table a 
    WHERE (`firstname`,`lastname`) IN (
        SELECT `firstname`,`lastname` FROM table 
        GROUP BY `firstname`,`lastname` HAVING COUNT(*)>1       
        )  
    )z WHERE z.`objid` NOT IN (
        SELECT MIN(`objid`) FROM table 
        GROUP BY `firstname`,`lastname` HAVING COUNT(*)>1
        )

Upvotes: 0

Jeetendra singh negi
Jeetendra singh negi

Reputation: 19

nice option get all duplicated value from tables 

 select * from Employee where Name in (select Name from Employee group by Name having COUNT(*)>1)

Upvotes: 1

Santhi Kabir
Santhi Kabir

Reputation: 291

This answer may not be great one, but I think it is simple to understand.

SELECT * FROM table1 WHERE (firstname, lastname) IN ( SELECT firstname, lastname FROM table1 GROUP BY firstname, lastname having count() > 1);

Upvotes: 0

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34591

SELECT DISTINCT t1.*
FROM myTable AS t1
INNER JOIN myTable AS t2
  ON t1.firstname = t2.firstname
  AND t1.lastname = t2.lastname
  AND t1.objid <> t2.objid

This will output every row which has a duplicate, basing on firstname and lastname.

Upvotes: 8

vol7ron
vol7ron

Reputation: 42109

Here's a little more legible way to do Ben's first answer:

WITH duplicates AS (
   select    firstname, lastname
   from      my_table
   group by  firstname, lastname
   having    count(*) > 1
)
SELECT    a.*
FROM      my_table   a
JOIN      duplicates b ON (a.firstname = b.firstname and a.lastname = b.lastname)

Upvotes: 6

Ben
Ben

Reputation: 52863

You've found your duplicated records but you're interested in getting all the information attached to them. You need to join your duplicates to your main table to get that information.

select *
  from my_table a
  join ( select firstname, lastname 
           from my_table 
          group by firstname, lastname 
         having count(*) > 1 ) b
    on a.firstname = b.firstname
   and a.lastname = b.lastname

This is the same as an inner join and means that for every record in your sub-query, that found the duplicate records you find everything from your main table that has the same firstseen and lastseen combination.

You can also do this with in, though you should test the difference:

select *
  from my_table a
 where ( firstname, lastname ) in   
       ( select firstname, lastname 
           from my_table 
          group by firstname, lastname 
         having count(*) > 1 )

Further Reading:

Upvotes: 62

Related Questions