Sql: How to Find Duplicates Based on Two Fields

How do I find duplicates across multiple columns?

Duplicated id for pairs name and city:

select s.id, t.* 
from [stuff] s
join (
    select name, city, count(*) as qty
    from [stuff]
    group by name, city
    having count(*) > 1
) t on s.name = t.name and s.city = t.city

Find duplicate records based on two columns

Instead of a grouped COUNT you can use it as a windowed aggregate to access the other columns

SELECT fullname,
       address,
       city
FROM   (SELECT *,
               COUNT(*) OVER (PARTITION BY fullname, city) AS cnt
        FROM   employee) e
WHERE  cnt > 1 

SQL: How to find duplicates based on two fields?

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (PARTITION BY station_id, obs_year ORDER BY entity_id) AS rn
        FROM    mytable t
        )
WHERE   rn > 1

SQL select duplicate rows based on multiple columns

Use a derived table to get your base values and join it back to the original table.

SELECT 
  a.id,
  b.id as AlternateID,
  a.value
FROM 
  (SELECT MIN(id) as id , value FROM YourTable GROUP BY value) a
JOIN YourTable b on a.value = b.value and a.id <> b.id

Query for finding duplicates based on two columns

You can use window function :

select t.*
from (select t.*, 
             count(*) over (partition by cola) as cola_cnt,
             count(*) over (partition by colb) as colb_cnt
      from table t
     ) t
where cola_cnt = 1 and colb_cnt = 1;

Identify duplicates rows based on multiple columns

Your sample data does not make it completely clear what you want here. Assuming you want to target groups of records having duplicate first/second columns with all third column values being unique, then we may try:

SELECT ID, NAME, DEPT
FROM
(
    SELECT ID, NAME, DEPT,
        COUNT(*) OVER (PARTITION BY ID, NAME) cnt,
        MIN(DEPT) OVER (PARTITION BY ID, NAME) min_dept,
        MAX(DEPT) OVER (PARTITION BY ID, NAME) max_dept
    FROM yourTable
) t
WHERE cnt > 1 AND min_dept = max_dept;

How to find duplicates in 2 columns not 1

You should set up a composite key between the two fields. This will require a unique stone_id and upcharge_title for each row.

As far as finding the existing duplicates try this:

select   stone_id,
         upcharge_title,
         count(*)
from     your_table
group by stone_id,
         upcharge_title
having   count(*) > 1

Find duplicates based on two columns

You really need to post more details to make this more clear and easier for the people helping but I think this is pretty close to what you are looking for.

with FindDupes as
(
    select LoadManifestID
        , VINid
        , ROW_NUMBER() over(partition by LoadManifestID, VINid order by EntryLineNo) as RowNum
    from tblMR
)

update m
set IsDeleted = 1
from tblMR m
join FindDupes d on d.LoadManifestID = m.LoadManifestID 
                and d.VINid = m.VINid
where d.RowNum > 1

---

Related Topics