0

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...)
Thom A
  • 88,727
  • 11
  • 45
  • 75
D. Fowler
  • 5
  • 2
  • This is for PostgreSQL, but the solution for SQL Server is identical in this case: [SQL to select all rows with duplicate values in one column](https://stackoverflow.com/questions/36384107/sql-to-select-all-rows-with-duplicate-values-in-one-column) – Thom A Jan 17 '23 at 12:30
  • @Larnu I have modified the examples in that question and they all seem to just be returning every single instance in the table, not exactly what I am looking for – D. Fowler Jan 17 '23 at 12:37
  • I would suggest you've not implemented the solution properly then. – Thom A Jan 17 '23 at 12:37
  • `select site_id, site_name, site_address from (select *, min(site_address) over(partition by site_name) as minsite_address, max(site_address) over(partition by site_name) as maxsite_address from tableX) as t where minsite_address <> maxsite_address` – lptr Jan 17 '23 at 15:39

2 Answers2

0

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)
Jonxag
  • 766
  • 7
  • 20
0

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

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Doing this returns the ID and Name for all 20,000 rows in my table. I am looking for just the distinct scenarios where the same name has more than 1 distinct id – D. Fowler Jan 17 '23 at 12:42
  • Then all 20,000 of the rows have at least one other row with the same `SiteID`, @D.Fowler . – Thom A Jan 17 '23 at 12:44
  • For the sample data you've given, I only get the rows where `SiteId` has a value of `1`, `3`, and `5`. See the Fiddle I've added to the answer, @D.Fowler . – Thom A Jan 17 '23 at 12:48