How can I delete the duplicate rows while keeping the original record?

Question

I have an one table as below a picture which indicates some duplicated rows.I can find the duplicated rows but I could not able to delete it because of there is no any unique ID that I can distinguish. There were lots of duplicated rows like that in same table I just screenshot a piece of that.

As a result,according to the below picture, how can I delete the duplicated rows but keep original ?

MySQL or T-SQL? One option, depending on your table size, could just be to Create a temporary table. Fill it with unique records by doing a `SELECT DISTINCT` from your current table. Truncate your current table. Copy the entire temp table to the truncated table. — Tyler Roper, Oct 19 '16 at 13:40
Possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Ivar, Oct 19 '16 at 13:43
@Ivar this is a slightly different issue, in that there is no primary key or unique identifier. — Tyler Roper, Oct 19 '16 at 13:44
T-SQL. I tried that but every 5 minutes there were some Incoming data to this table and I could not stop that. That is why I need to delete the duplicated rows at the same time — Semih Ural, Oct 19 '16 at 13:48

score 0 · Answer 1 · answered Oct 19 '16 at 13:48

One solution you could consider is copying all unique records into a temporary table, thus removing the duplicates. You could then truncate the original table and re-populate it from the temporary table you've created. The code would be something like this:

SELECT DISTINCT * INTO #tempTable FROM MyTable

TRUNCATE TABLE MyTable;
INSERT INTO MyTable (LocationID, UnitID, CameraID ... IsActiveHours) 
SELECT LocationID, UnitID, CameraID ... IsActiveHours FROM #tempTable;

This isn't always an option due to key constraints and amount of data, but useful in certain cases. Take it as you may.

score 0 · Answer 2 · answered Oct 19 '16 at 13:55

You could use a cte and Row_Number() to accomplish this. If you are satisfied with the results, remove the final select and un-comment the delete statement

;with cte as (
    Select *,RowNr=Row_Number() over (Partition By LocationId Order by Date_T) 
     From   YourTable
)
Select * from cte Where RowNr>1
-- Delete From cte Where RowNr>1

Semih Ural · Answer 3 · 2016-10-20T06:41:50.790

0

This is the query that fix this issue.

    WITH X AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY  LocationId,date_t ORDER BY LocationId  desc) as 'rownum',LocationId,
    date_T AS T
    FROM Counts
    )
    --SELECT * FROM X WHERE rownum >1 

DELETE FROM X
WHERE rownum <> 1

edited Oct 20 '16 at 06:41

answered Oct 19 '16 at 14:35

Semih Ural

47
1
10

score 0 · Answer 4 · answered Oct 19 '16 at 15:56

You would be best adding an identity column to make things easier however this can be done without a TRUNCATE using the following:

    --GET DUPLICATE ROWS INTO A TEMP TABLE (YOU MAY NOT NEED TO USE ALL THE COLUMNS TO IDENTIFY A DUPLICATE)
    SELECT ROW_NUMBER() OVER (ORDER BY ColA) AS RowNo, ColA, ColB, ColC, COUNT(*) As [Count] 
    INTO #TEMP1
    FROM test 
    GROUP BY ColA, ColB, ColC
    HAVING COUNT(*) > 1

    --LOOP THROUGH DUPLICATES
    DECLARE @RowNo INT
    DECLARE @Duplicates INT

    SET @RowNo = 1

    WHILE EXISTS(SELECT * FROM #TEMP1)
    BEGIN

        --GET A COUNT OF ADDITIONAL ROWS FOR THIS DUPLICATE
        SET @Duplicates = (SELECT [Count] FROM #TEMP1 WHERE RowNo = @RowNo) - 1

        --DELETE THE ROWS WE DONT NEED
        DELETE TOP (@Duplicates) t1
        FROM test t1
        JOIN #TEMP1 t2 ON t1.ColA = t2.ColA AND t1.ColB = t2.ColB AND t1.ColC = t2.ColC
        WHERE t2.RowNo = @RowNo

        --REMOVE THE ROW FROM THE TEMP TABLE
        DELETE FROM #TEMP1 WHERE RowNo = @RowNo

        --INCREASE THE ROW NO TO MOVE TO THE NEXT ROW
        SET @RowNo = @RowNo + 1
    END

    --DROP THE TEMP TABLE
    DROP TABLE #TEMP1

How can I delete the duplicate rows while keeping the original record?

4 Answers4