CODEWITHSUNDEEP
sql-server
Mark Blackburn
Mark Blackburn

Reputation: 105

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

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:

Result

Upvotes: 1

Views: 64

Answers (2)

PSK
PSK

Reputation: 17943

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

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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];

Upvotes: 0

Related Questions