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.

Basically: SELECT ContactID, MAX(LeadSalePrice) FROM Blah GROUP BY ContactID — DavidG
– DavidG, Commented Feb 8, 2017 at 22:49

Gordon Linoff · Accepted Answer · 2017-02-08 22:49:45Z

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;

John Cappelletti · Accepted Answer · 2017-02-08 23:03:25Z

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

Collectives™ on Stack Overflow

Eliminate a duplicate result from a single column

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related