Show all rows that have certain columns duplicated

by

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:

More Related Contents:

  1. Efficiently convert rows to columns in sql server
  2. T-SQL split string
  3. How can I get column names from a table in SQL Server?
  4. How can I select the first day of a month in SQL?
  5. GROUP BY to combine/concat a column [duplicate]
  6. Select statement to find duplicates on certain fields
  7. How to turn IDENTITY_INSERT on and off using SQL Server 2008?
  8. Using a Variable in OPENROWSET Query
  9. How to find current transaction level?
  10. EXISTS vs JOIN and use of EXISTS clause
  11. Check for file exists or not in sql server?
  12. How to cast variables in T-SQL for bulk insert?
  13. How to create a PivotTable in Transact/SQL?
  14. Group DateTime into 5,15,30 and 60 minute intervals
  15. concatenate two database columns into one resultset column
  16. Why is a UDF so much slower than a subquery?
  17. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  18. T-SQL and the WHERE LIKE %Parameter% clause
  19. Custom aggregate function (concat) in SQL Server
  20. UNIX_TIMESTAMP in SQL Server
  21. Which SQL query is faster? Filter on Join criteria or Where clause?
  22. UPDATE and REPLACE part of a string
  23. Improve SQL Server query performance on large tables
  24. Average of multiple columns
  25. Add emoji / emoticon to SQL Server table
  26. Cumulative Total in MS Sql server [duplicate]
  27. How to write a WHERE Clause to find all records in a specific month?
  28. How to make a view column NOT NULL
  29. Flattening of a 1 row table into a key-value pair table
  30. SQL Server equivalent of WM_CONCAT function [duplicate]

Leave a Comment