-3

I have written code to find duplicate records in a SQL Server table when 5 columns match, please confirm this code is correct or advise any changes.

SQL:

select UAID, URA_ID, UADesc,URADesc, URASDHCode, COUNT(*)
from table
where UAID = UAID
group by UAID, URA_ID, UADesc,URADesc, URASDHCode
having COUNT(*)>1

Retrieve records only when 5 columns match, there are more than 20 columns in a table.

Dale K
  • 21,987
  • 13
  • 41
  • 69
Raj
  • 45
  • 7
  • 1
    what's the purpose of where UAID = UAID? – Ed Bangga Sep 04 '19 at 00:48
  • UAID is primary Key of this table. – Raj Sep 04 '19 at 00:50
  • Please read [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952/62576) Also, you should know if this works or not by looking at the results to see if they are the records you expect to find as duplicates or not. We can't, because we don't have your data. – Ken White Sep 04 '19 at 00:56
  • Possible duplicate of [Finding duplicate rows in SQL Server](https://stackoverflow.com/questions/2112618/finding-duplicate-rows-in-sql-server) – Ed Bangga Sep 04 '19 at 00:57
  • Sorry here the primary key is not repeated for duplicate records since other fields are having different values, but there are more than one records with 5 columns populated with same values, I need to retrieve those records for which the 5 columns are repeated. – Raj Sep 04 '19 at 01:05

1 Answers1

-1

This should do it. Use row_number with CTE to get row numbers if you wish do delete duplicates.

select UAID, URA_ID, UADesc, URADesc, URASDHCode, COUNT(*)
from table
group by UAID, URA_ID, UADesc, URADesc, URASDHCode
having COUNT(*)>1
Neeraj Agarwal
  • 1,064
  • 4
  • 5