SQL Server: How to de-duplicate on two columns/conditions?

user_85248 0
Viewed 61

I have a database table that has duplicate rows for addresses. For the duplicate address rows, I want to keep a single row with the highest value from one column, and in the cases where there are still duplicates then just choose one (doesn't matter, no way to know which is better as these are arbitrary string values, max/min should be sufficient). For example, let's say we have the following table with two addresses, one of which is duplicated four times:

ADDR1       CITY    STATE   APN     CONFIDENCE
1 Main St.  Dover   IL      apn-1   7
1 Main St.  Dover   IL      apn-2   8
1 Main St.  Dover   IL      apn-3   5
1 Main St.  Dover   IL      apn-4   8
7 Ajax Rd.  Dover   IL      apn-8   9

I need to filter out duplicate rows to only those with the highest confidence score for the address, using a group by on the address columns, and from that "sub-select" we then select the max or min of the ungrouped column (APN in this example) to get the following desired result:

ADDR1       CITY    STATE   APN     CONFIDENCE
1 Main St.  Dover   IL      apn-4   8
7 Ajax Rd.  Dover   IL      apn-8   9

I have come up with an SQL query that works:

select sub.ADDR1, sub.CITY, sub.STATE, MAX(APN), sub.CONFIDENCE
from example_db.example_table as table1
left join (
    select ADDR1,
           CITY,
           STATE,
           MAX(CONFIDENCE) as CONFIDENCE
    from example_db.example_table
    group by ADDR1,
             CITY,
             STATE
) as sub
on (table1.ADDR1 = sub.ADDR1)
and (table1.CITY = sub.CITY)
and (table1.STATE = sub.STATE)
group by sub.ADDR1, sub.CITY, sub.STATE, sub.CONFIDENCE

The above is a simplified example to demonstrate my use case, but the data I will run this SQL code on is large (~130M rows) and the SQL Server instance is meager -- I don't want to overload it if I can optimize the above or use another, more efficient approach.

greatest-n-per-group where-clause tsql sql-server sql
2 Answers

You can use window functions:

select *
from (
    select t.*, 
        row_number() over(partition by addr1, city, state order by confidence desc) rn
    from mytable t
) t
where rn = 1
user_106767160

Just another option is using WITH TIES

Not exactly clear if the APN is required in the order by

Example

Select top 1 with ties * 
 From  YourTable
 Order by row_number() over (partition by addr1,city,state order by confidence desc, apn)

Returns

 ADDR1      CITY    STATE     APN     CONFIDENCE
1 Main St.  Dover   IL        apn-2   8
7 Ajax Rd.  Dover   IL        apn-8   9
user_15700000
Related