Eliminate a duplicate result from a single column

Question

Let's say I have a result from a query that looks like this:

ContactID     LeadSalePrice
---------------------------
   45           19.90
   45           18.00
   32           17.50

But, I want to eliminate duplicate ContactID's, always taking the higher price result. So what I want is:

ContactID     LeadSalePrice
---------------------------
   45           19.90
   32           17.50

Here's (a simplified version of) the query:

SELECT 
    sc.ContactID
    , c.LeadSalePrice
FROM
    LeadSalesCampaignCriterias c
    JOIN LeadSalesCampaigns sc ON c.LeadSalesCampaignID = sc.LeadSalesCampaignID
WHERE
    ...
ORDER BY 
    LeadSalePrice DESC

I've been playing around with DISTINCT and GROUP BY, but I'm not getting it.

Answer 1

Just use GROUP BY:

SELECT sc.ContactID, MAX(c.LeadSalePrice) as LeadSalePrice
FROM LeadSalesCampaignCriterias c JOIN
     LeadSalesCampaigns sc
     ON c.LeadSalesCampaignID = sc.LeadSalesCampaignID
WHERE ...
GROUP BY sc.ContactID;

Answer 2

Another option is the WITH TIES and Row_Number()

Select Top 1 with Ties *
 From YourTable 
 Order By Row_Number() over (Partition By ContactID Order By LeadSalePrice Desc)

Returns

ContactID   LeadSalePrice
32          17.50
45          19.90