SQL Query that return a duplicate record

Adalat Khan

6y1.5kSQL

We have a table Cells that contains the following fields:

SiteId, CellId, Technology, Band, CellName

The first four columns SiteId, CellId, Technology, and Band actually behaves like a primary key and each combination of these four columns must be unique but unfortunatelly we have some duplicate records in our table. Following is my scenario: Consider the following few records:

SiteId CellId Technology Band CellName

1001 1 2G 800 1001A

1001 1 3G 800 1001B

1001 1 4G 800 1001C

The above three records are unique because in the first cour columns the Technology column has different values. Now consider the following record:

SiteId CellId Technology Band CellName

1001 1 3G 800 1001A

1002 1 3G 800 1001A

Now in the above records the first two records are duplicated because the SiteId, the CellId, the Technology, and the Band value of the first two records are the same.

I want to display all duplicated records from the above table.

Note: Two records will be considered duplicate if their SiteId, CellId, Technology and Band are same.

Anyone please help me in this regord and send me a query that displays a duplicate record from the above table in the above senarion.

Thanks.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anoop Kumar Sharma6y ago
Accepted answer
Hello Adalat Khan,

Please use the below-shared query in order to find the duplicate records in the table you have. Please make sure to replace your table name (i.e. YourTable) with the Actual Table Name.
--Create a row_number with partition by in order to find the duplicate record
With CTEDuplicate as
(
Select SiteID,CellID,Technology,Band,CellName,row_number() over(partition by SiteID,CellID,Technology,Band Order by SiteID,CellID,Technology,Band)
as DuplicateCheck
from YourTable
)

--Return All the Columns from YOURTABLE which are duplicated based upon siteid, cellid, technology and band
Select yt.* from YourTable yt
inner join
(
Select Distinct SiteId,CellID,Technology,Band from CTEDuplicate where DuplicateCheck>1
)DuplicateRec on yt.SiteId=DuplicateRec.SiteId and yt.CellId=DuplicateRec.CellId and yt.Technology=DuplicateRec.Technology and yt.Band=DuplicateRec.Band
Hope this will help you.
Thanks,
+5
Jignesh Kumar6y ago
Hi,

There are multiple ways to achive this one, You can create Common table expresson and find out,
WITH CTEDuplicate AS (
    SELECT
        SiteId,CellId,Technology,Band,
        ROW_NUMBER() OVER (
            PARTITION BY SiteId,CellId,Technology,Band
            ORDER BY SiteId,CellId,Technology,Band) rownum
    FROM YourTableName
)
SELECT SiteId,CellId,Technology,Band, rownum as NumberofRecords FROM CTEDuplicate WHERE     rownum > 1;
Please refer this link where you will find multiple ways to do this,
https://www.sqlservertutorial.net/sql-server-basics/sql-server-find-duplicates/
+3
Anoop Kumar Sharma6y ago
Hi Adalat Khan,

Yes, Row_Number() is built in function to generate Number of Current row within its partition.

For MySQL, please refer to the below MySQL Documention:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

For more examples, refer:
https://www.sqlshack.com/overview-of-the-sql-row-number-function/
+3
Sachin Singh6y ago
@Adalat Khan,
Yes Row_Number() is a built-in function in Sql server(all version) and MySql (version 8.0 and +)
It assigns each row a squential Number based on the column specified under OrderBy and PartionBy of this function.
+1
Adalat Khan6y ago
Dear Annop Kumar could you please explain the row_number(). Is this builtin function?. I am using MySql. Your query looks good but please explain row_number().
+2
Sandeep Singh6y ago
I think Query shared by Sachin does not have CellName column in output. It's Incompeted data as In the output requested by Adalat khan have CellName Column as well, I think answer shared by Anoop will work so giving him +1 for that.
+4
Sachin Singh6y ago
It will return all rows which are duplicated
SELECT
    SiteId,CellId,Technology,Band,
    COUNT(*)
FROM
    table_name
GROUP BY
       SiteId,CellId,Technology,Band
HAVING
    COUNT(*) > 1;
+4