Remove duplicates in single column from view in SQL Server?

Question

I (beginner SQL user) have a SQL Server view, created with the following statement:

CREATE VIEW dbo.v_LSitesGeo AS

SELECT   PS.LSiteID, GEO.geographyColumn
FROM         dbo.Project_SiteID_Lookup AS PS INNER JOIN
             dbo.Geocodio AS GEO ON GEO.ProjectCode = PS.ProjectNumber
WHERE     (NOT (GEO.geographyColumn IS NULL)) AND (NOT (PS.LSiteID IS NULL))

However, the [LSiteID] column has duplicate values in it. I want to filter out any duplicates in the view however, they need to stay in the [Project_SiteID_Lookup] table. All of the [ProjectNumber] values are unique, so I can't use DISTINCT.

I tried to follow this post, however I wasn't able to get the following to work:

SELECT   PS.LSiteID, GEO.geographyColumn

FROM         dbo.Project_SiteID_Lookup AS PS INNER JOIN 
             dbo.Geocodio AS GEO ON GEO.ProjectCode = PS.ProjectNumber


WHERE       0 = (SELECT COUNT(PS.LSiteID) 
            FROM dbo.Project_SiteID_Lookup AS PS, dbo.Project_SiteID_Lookup AS PS2
            WHERE PS2.LSiteID = PS.LSiteID
            AND PS2.LSiteID < PS.LSiteID)

Any advice on how to make the above work, or another method to achieve this, would be much appreciated.

"...I want to filter out any duplicates..." -- So, if the view produces three rows with the same `LSiteID`, do you want to keep the first row "as is", and replace `LSiteID` by null in the second and third one? — The Impaler, Jun 29 '22 at 17:00
@traitortots What about the second and third rows? Do you want to remove them altogether from the view or do something else? — The Impaler, Jun 29 '22 at 17:10
@TheImpaler I'd like to remove them altogether (the duplicates are when multiple projects are connected to one site, and I only need the locations of each site) — traitortots, Jun 29 '22 at 17:17

score 0 · Accepted Answer · answered Jun 29 '22 at 17:20

You can use ROW_NUMBER() to identify the first row of each group. Then, filtering out the other rows is easy. For example:

select *
from (
  SELECT
    PS.LSiteID,
    GEO.geographyColumn,
    row_number() over(partition by PS.LSiteID order by PS.LSiteID) as rn
  FROM dbo.Project_SiteID_Lookup AS PS 
  INNER JOIN dbo.Geocodio AS GEO ON GEO.ProjectCode = PS.ProjectNumber
  WHERE (NOT (GEO.geographyColumn IS NULL)) 
    AND (NOT (PS.LSiteID IS NULL))
) x
where rn = 1

This did the trick! Thank you!! – traitortots Jun 29 '22 at 17:34 — traitortots, Jun 29 '22 at 17:34

score 0 · Answer 2 · answered Jun 29 '22 at 17:22

You do not want to filter the duplicates; you want to aggregate them. This is an important difference, which quite directly points you to aggregation, that is, to group by:

SELECT   PS.LSiteID, STRING_AGG(GEO.geographyColumn, ',')
FROM         dbo.Project_SiteID_Lookup AS PS INNER JOIN
             dbo.Geocodio AS GEO ON GEO.ProjectCode = PS.ProjectNumber
WHERE     (NOT (GEO.geographyColumn IS NULL)) AND (NOT (PS.LSiteID IS NULL))
GROUP BY PS.LSiteID

No worries about the duplicates, the group by resolves it.

Sorry, I should have mentioned - I'm on SQL Server 2016, and it looks like STRING_AGG isn't available — traitortots, Jun 29 '22 at 17:35

Remove duplicates in single column from view in SQL Server?

2 Answers2