Remove Duplicates from SQL Query

Question

I am using this query currently to trigger a customer event. But sometimes the same customer will be in the results because they are assigned a different WorkOrderId, which is my primary filter. So, I want to expand the filter to also look for a unique CustomerName. In other words, if the query returns two rows with the same CustomerName then I want it to exclude the 2nd row altogether.

SELECT CustomerName, JobId, Email, CCEmail, WorkOrderId AS id
FROM dbo.vwWorkOrderDetail
WHERE JobStatusId=3 AND Active=1 AND LocationStopTypeId=1
ORDER BY WorkOrderId DESC

I've tried using DISTINCT but I continue to get results that include the same CustomerName in different rows. How can I setup this query so it returns results that passed all of the WHERE conditions and then only shows rows with a unique CustomerName?

Your question is incomplete. What do you want to do about the fact that there are two workOrderId's? — Dan Bracuk
– Dan Bracuk, Commented Nov 1, 2016 at 18:32
Do you always want the result of this query to produce one or zero rows? — JohnH
– JohnH, Commented Nov 1, 2016 at 18:36

JamieSee · Accepted Answer · 2016-11-01 18:53:43Z

1

As long as you include WorkOrderId, DISTINCT will do nothing for you. DISTINCT can only eliminate duplicates where all of the columns specified in the SELECT contain the same information. So to use DISTINCT to eliminate duplicate customers, you would need to do this:

SELECT DISTINCT CustomerName, JobId, Email, CCEmail
FROM dbo.vwWorkOrderDetail
WHERE JobStatusId=3 AND Active=1 AND LocationStopTypeId=1
ORDER BY WorkOrderId DESC

The best way to approach this to preserve a WorkOrderId is to make a new view based on the underlying tables. You will need to decide what WorkOrderId of the available WorkOrderIds you want to present. Typically this is the highest ID. If all you need is the WorkOrderId itself and not the details, this is actually pretty simple. Note the code below is a naïve example that assumes CustomerId is tied directly to a work order. To really answer this properly you'd need to provide the code for vwWorkOrderDetail.

SELECT CustomerName, JobId, Email, CCEmail, (SELECT MAX(WorkOrderId) FROM WorkOrders WHERE CustomerID = Customers.CustomerID) AS WorkOrderID
FROM Customers
WHERE JobStatusId=3 AND Active=1 AND LocationStopTypeId=1
ORDER BY WorkOrderId DESC

answered Nov 1, 2016 at 18:53

JamieSee

13.1k2 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

STampa Over a year ago

Gotcha. Well, I looked at the design layout for the DB view and CustomerID is directly connected via the 'Customer' table. It looks like the CustomerID and CustomerName are the only values on 'Customer', the email data is on the 'Contact' table. This is a complex view that joins more than 8 tables, unfortunately. I appreciate the insight and response, just not sure I have the SQL knowledge to answer the other aspects of the question :(

JamieSee Over a year ago

If you post the view code I can try to help. The design questions that need to be answered are what work order to return if only one is desired and whether or not any of the work order details other than the WorkOrderId are required. There are a variety of ways to accomplish this stuff, but choosing the right one depends on receiving good requirements.

S3S · Accepted Answer · 2016-11-01 19:07:38Z

0

SELECT CustomerName, JobId, Email, CCEmail, max(WorkOrderId) AS id
FROM dbo.vwWorkOrderDetail
WHERE JobStatusId=3 AND Active=1 AND LocationStopTypeId=1
GROUP BY CustomerName, JobId, Email, CCEmail

edited Nov 1, 2016 at 19:07

S3S

25.2k5 gold badges29 silver badges48 bronze badges

answered Nov 1, 2016 at 19:01

ps.

4,3605 gold badges34 silver badges39 bronze badges

1 Comment

STampa Over a year ago

Thanks for the response! Results still show duplicate CustomerName results. Duplicate name results will also have a different WorkOrderId, JobId (or both) than the first record for that CustomerName. That's my issue. The primary filter sorts out 95% of the results which may have duplicates, so I'm left trying to filter that remaining 5% by the best option I have, CustomerName.

Collectives™ on Stack Overflow

Remove Duplicates from SQL Query

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related