Show all rows that have certain columns duplicated

Question

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

Community · Accepted Answer · 2017-05-23 12:09:56Z

62

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 )

11 Comments

nee21 Over a year ago

does this syntax work in sql server? where ( firstname, lastname ) in ( select firstname, lastname from my_table group by firstname, lastname having count(*) > 1 )

Ben Over a year ago

Have you run it @nee21? What problems did you have?

nee21 Over a year ago

Yes, I get this error: Msg 4145, Level 15, State 1, Line 161 An expression of non-boolean type specified in a context where a condition is expected, near ','. I am not sure if am missing anything.

nee21 Over a year ago

Hi @Ben, did u get a chance to check this?

CSK Over a year ago

i don't think it works on sql server @Ben. op put the sql server 2008 as label

|

Dmytro Shevchenko · Accepted Answer · 2012-04-25 21:48:43Z

8

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.

edited Apr 25, 2012 at 21:48

answered Apr 25, 2012 at 21:36

Dmytro Shevchenko

34.9k6 gold badges56 silver badges68 bronze badges

2 Comments

vol7ron Over a year ago

instead of id you might have meant objid

Mosty Mostacho Over a year ago

Additionally, if you don't distinct the results you'll get duplicates.

vol7ron · Accepted Answer · 2012-04-25 21:48:01Z

6

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)

answered Apr 25, 2012 at 21:48

vol7ron

42.3k22 gold badges126 silver badges178 bronze badges

7 Comments

Dmytro Shevchenko Over a year ago

Wouldn't a simple join (like in my answer) be faster than joining to a grouped temporary table?

vol7ron Over a year ago

@Shedal: they should be the same thing. A subquery is a temporary table. The above is a way of simplifying reading the SQL. By doing your declaring/defining your subqueries up front, you're able to concentrate on the heart of the SQL that follows

Ben Over a year ago

@Shedal, it depends. If there's an index on firstname, lastname for instance ( I +1'd you though as it's just a different way of doing things ).

Dmytro Shevchenko Over a year ago

@Ben well there should be an index on firstname, lastname anyway, for both queries to run fast.

Ben Over a year ago

@Shedal, the sub-query will only use the index though, the join will have to either use two indexes ( unless it's indexed on obj_id, fn, ln) or enter the table. Plus there's no need to do a distinct. Without testing and knowing the selectivity of the columns it's impossible to tell which'll be faster.

|

Autumn Skye · Accepted Answer · 2018-03-28 12:32:08Z

6

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)

edited Mar 28, 2018 at 12:32

Autumn Skye

7,56914 gold badges74 silver badges98 bronze badges

answered Mar 28, 2018 at 11:37

Mahesh Bharati

611 silver badge1 bronze badge

1 Comment

Thomas Flinkow Over a year ago

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

Jeetendra singh negi · Accepted Answer · 2014-09-26 17:44:33Z

1

nice option get all duplicated value from tables

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

answered Sep 26, 2014 at 17:44

Jeetendra singh negi

191 bronze badge

Comments

Riccardo · Accepted Answer · 2016-06-20 09:05:44Z

1

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)

answered Jun 20, 2016 at 9:05

Riccardo

112 bronze badges

Comments

Sebastian Lenartowicz · Accepted Answer · 2016-09-18 00:25:45Z

1

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)

edited Sep 18, 2016 at 0:25

Sebastian Lenartowicz

4,8865 gold badges31 silver badges41 bronze badges

answered Sep 17, 2016 at 4:04

pooja

111 bronze badge

Comments

scientific_explorer · Accepted Answer · 2019-05-03 18:02:17Z

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.

Santhi Kabir · Accepted Answer · 2014-03-27 05:56:02Z

0

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);

answered Mar 27, 2014 at 5:56

Santhi Kabir

2911 gold badge3 silver badges8 bronze badges

Comments

Mohammed Safeer · Accepted Answer · 2014-10-11 19:22:40Z

0

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
        )

edited Oct 11, 2014 at 19:22

answered May 3, 2014 at 7:45

Mohammed Safeer

21.6k8 gold badges79 silver badges81 bronze badges

Comments

Md. Nazmul Alom · Accepted Answer · 2021-04-07 15:11:01Z

0

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

answered Apr 7, 2021 at 15:11

Md. Nazmul Alom

3092 silver badges5 bronze badges

Collectives™ on Stack Overflow

Show all rows that have certain columns duplicated

11 Answers 11

Further Reading:

11 Comments

2 Comments

7 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

Further Reading:

11 Comments

2 Comments

7 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related