How to remove duplicates (other than using Distinct)?

Other SQL Server Topics
1

I have a fairly simple query that pulls in duplicated amounts that I want to remove.

SELECT
od.parentorganizationname
,cdc.CountyName
,ad.AllocationAmt

FROM (I've abbreviated the actual tables and joins)
odtable as od
cdctable as cdc
adtable as ad

Results look like this:

|parentorganizationname |CountyName| AllocationAmt |

|Provider A |Scotty| $68,168.29 |
|Provider B| Toner| $144,411.00 |
|Provider B |Booker| $144,411.00 |
|Provider B |Tracker| $144,411.00 |
|Provider B| GREENE| $144,411.00 |
|Provider B| GREENE| $144,411.00 |
|Provider B| GREENE| $144,411.00 |
|Provider B| MORGAN| $144,411.00 |
|Provider C| STE GENEVIEVE| $31,960.00 |
|Provider C| CAPE GIRARDEAU| $31,960.00 |

My goal is to set up the query so that the results do not show the duplicate Allocation Amounts.
In other words, Provider A would show their amount, Provider B would show the $144,411 amount but not keep repeating.
I hope this is sufficient info given for what I'm asking for.
Thank you for any and all help!!

2

Lookup ROW_NUMBER() - you can set that up with a partition on parent and county - order it by some column (usually a datetime in descending order to get the latest row identified as row 1).

Using either a CTE or derived table - you can then filter where row = 1.

3

Why not use DISTINCT?

Also, you may need to adjust the joins to not return duplicates in the first place, rather than removing them later. But of course we'd have to see all of the join logic, and possibly the table DDL, to be sure of how to do that in the original query.

1 Like
4

https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/

5

I've seen CTE somewhere before but have never tried. I'll look into that further and see if that may work in this scenario.
Thank you!