group by
-
I need to return 2 columns in a group by using a having clause
I only want results if the data in one column has duplicates
(they arent strictly speaking duplicates, since there are several towns with the same name)
something like this
select TownName, CountyName FROM Town
INNER JOIN County
ON County.CountyID = Town.CountyID
GROUP BY TownName, CountyName
having count(TownName) > 1
this returns no data, but there are duplicate town names in the town table, so id want to return those along with the associated county
-
Try this
WITH CTE AS (SELECT TownName, CountyName,
COUNT(*) OVER(PARTITION BY TownName) AS cn
FROM Town
INNER JOIN County ON County.CountyID = Town.CountyID
)
SELECT TownName, CountyName
FROM CTE
WHERE cn>1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
that worked perfectly, thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply