Get Different Distinct Pairs-CodePudding

I have a table containing a site id, site name, and site address. I am trying to write up a query that will give me all instances of the same site name having a different site id and am having a difficult time..

site_id      site_name      site_address
  1        North Office       123 Test Rd.
  2           Eastern         456 Fake St.
  3        North Office       789 Data Ln.
  4        West Office        111 Western Ave.
  5        North Office       901 Delta Rd.

In my scenario I would be looking for a query that returns site ids 1, 3, and 5 because the ids are different but the site name is the same.

I haven't gotten very far in what I have tried because I am a bit lost. I have tried incoroporating a COUNT with the HAVING and GROUP BY but I keep getting errors..

SELECT site_id, site_name, site_address FROM table WHERE site_id IN (SELECT site_id FROM table WHERE...)

CodePudding user response：

I can't seem to find a SQL Server duplicate, however, this is effectively the same answer as in many other RDBMS, and very similar the solution in something like Get top 1 row of each group; use a CTE/derived table to get the COUNT, and then filter on that:

WITH CTE AS(
    SELECT SiteId,
           SiteName,
           COUNT(*) OVER (PARTITION BY SiteName) AS Sites
    FROM dbo.YourTable)
SELECT SiteId,
       SiteName
FROM CTE
WHERE Sites > 1;

db<>fiddle

CodePudding user response：

You almost got it, probably failing on the group by. Here is a example of how it can be done

SELECT site_id, site_name, site_address
FROM dbo.YourTable
WHERE site_name IN (SELECT site_name FROM dbo.YourTable
GROUP BY site_name
HAVING COUNT(DISTINCT site_id) > 1)