SQL Server : remove duplicates based on one column of a SELECT

Question

I have the following query which works well. However, there are problems with the data and I need to remove the duplicate sVisitID's from the result. This is the SELECT I am using and the screenshot shows the result. I need to edit the select in order to only return one of the duplicates.

SELECT DISTINCT(MyReps.[sName]) as 'Rep Name', MyData.[sVisitID] as 'ID', CAST(MyData.[sDate] AS DATE) as 'Date', MyData.[sClientName] as 'Store Name', MyData.[sState] as 'Region', CAST(MyData.[sLatitudeStart] AS VARCHAR(100)) + ',' + CAST(MyData.[sLongitudeStart] as varchar(100)) as 'Map' FROM (SELECT * FROM [tblRepresentatives] WHERE [sActive] = 'True') as MyReps FULL OUTER JOIN (SELECT * FROM [tblVisits] WHERE CAST([sDate] AS DATE) = CAST(GETDATE()-1 AS DATE)) AS MyData ON MyReps.[sName] = MyData.[sRepresentativeName] WHERE MyData.[sClientName] IS NOT NULL AND [sDateAndTimeStart] <> [sDateAndTimeEnd] ORDER BY MyData.[sClientName] DESC

Results:

PSK · Accepted Answer · 2018-05-13 08:46:33Z

Another approach can be using ROW_NUMBER() PARTITION BY MyData.[sVisitID] like following.

SELECT * FROM ( SELECT (MyReps.[sName]) as 'Rep Name', MyData.[sVisitID] as 'ID', CAST(MyData.[sDate] AS DATE) as 'Date', MyData.[sClientName] as 'Store Name', MyData.[sState] as 'Region', CAST(MyData.[sLatitudeStart] AS VARCHAR(100)) + ',' + CAST(MyData.[sLongitudeStart] as varchar(100)) as 'Map', ROW_NUMBER() OVER (PARTITION BY MyData.[sVisitID] ORDER BY MyData.[sVisitID]) AS RN FROM ( SELECT * FROM [tblRepresentatives] WHERE [sActive] = 'True') as MyReps FULL OUTER JOIN ( SELECT * FROM [tblVisits] WHERE CAST([sDate] AS DATE) = CAST(GETDATE()-1 AS DATE)) AS MyData On MyReps.[sName] = MyData.[sRepresentativeName] WHERE MyData.[sClientName] IS NOT NULL AND [sDateAndTimeStart] <> [sDateAndTimeEnd] )T WHERE RN=1 ORDER BY T.[Store Name] DESC

Am I correct in assuming this would return 1 for the first occurrence and 2 for the 2nd? (ROW_NUMBER() OVER (PARTITION BY MyData.[sVisitID] ORDER BY MyData.[sVisitID]) AS RN)
You are right. For each duplicating id it will generate numbers 1..2 etc. Finally we are filtering and returning the first row.

Tim Biegeleisen · Accepted Answer · 2018-05-13 08:30:59Z

One option would be to use GROUP BY to resolve to a single record:

SELECT DISTINCT MyReps.[sName] AS [Rep Name], MyData.[sVisitID] AS [ID], CAST(MyData.[sDate] AS DATE) AS [Date], MyData.[sClientName] AS [Store Name], MyData.[sState] AS [Region], MIN(CAST(MyData.[sLatitudeStart] AS VARCHAR(100)) + ',' + CAST(MyData.[sLongitudeStart] AS varchar(100))) AS [Map] FROM your_cte GROUP BY [Rep Name], [ID], [Date], [Store Name], [Region];

Collectives™ on Stack Overflow

SQL Server : remove duplicates based on one column of a SELECT

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related