CODEWITHSUNDEEP
sqlsql-server
Gopal
Gopal

Reputation: 11982

How to find out the duplicate records

Using Sql Server 2000

I want to find out the duplicate record in the table

Table1

ID  Transaction Value

001 020102      10
001 020103      20
001 020102      10 (Duplicate Records)
002 020102      10
002 020103      20
002 020102      10 (Duplicate Records)
...
...

Transaction and value can be repeat for different id's, not for the same id...

Expected Output

Duplicate records are...

ID  Transaction Value  
001 020102      10 
002 020102      10 
...
...

How to make a query for view the duplicate records.

Need Query help

Upvotes: 3

Views: 16425

Answers (4)

Robert Koritnik
Robert Koritnik

Reputation: 105029

Self join (with additional PK or Timestamp or...)

I can see that people've provided solution with grouping but none has provided the self join solution. The only problem is that you'd need some other row descriptor that should be unique for each record. Be it primary key, timestamp or anything else... Suppose that the unique column's name is Uniq this would be the solution:

select distinct ID, [Transaction], Value
from Records r1
    join Records r2
    on ((r2.ID = r1.ID) and
        (r2.[Transaction] = r1.[Transaction]) and
        (r2.Value = r1.Value) and
        (r2.Uniq != r1.Uniq))

The last join column makes it possible to not join each row to itself but only to other duplicates...

To find out which one works best for you, you can check their execution plan and execute some testing.

Upvotes: 0

Sidharth Panwar
Sidharth Panwar

Reputation: 4654

Select Id, Transaction, Value, Count(id) 
from table 
group by Id, Transaction, Value
having count(id) > 1

This query will show you the count of times the ID has been repeated with each entry of the Id. If you don't need it you can simply remove the Count(Id) column from the select clause.

Upvotes: 0

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195992

You can use 

SELECT 
  ID, Transaction, Value
FROM 
  Table1
GROUP BY 
  ID, Transaction, Value
HAVING count(ID) > 1

Upvotes: 5

cjk
cjk

Reputation: 46425

You can do this:

SELECT ID, Transaction, Value
FROM Table
GROUP BY ID, Transaction, Value
HAVING COUNT(*) > 1

To delete the duplicates, if you have no primary key then you need to select the distinct values into a separate table, delete everything from this one, then copy the distinct records back:

SELECT ID, Transaction, Value
INTO #tmpDeduped
FROM Table
GROUP BY ID, Transaction, Value

DELETE FROM Table

INSERT Table
SELECT * FROM #tmpDeduped

Upvotes: -1

Related Questions