I was looking to identify the 'Email' that was duplicated.
The two simplest ways I thought of are shown below, however for some reason the query using the derived table produces the error: Incorrect syntax near ')'.
CREATE TABLE #TEMP ( ID INT PRIMARY KEY, Email VARCHAR(255) ) INSERT INTO #TEMP VALUES (1, 'firstname.lastname@example.org'), (2, 'email@example.com'), (3, 'firstname.lastname@example.org') -- Fails to run SELECT Email FROM ( SELECT Email, COUNT(*) AS Count_ FROM #TEMP GROUP BY Email HAVING COUNT(*) > 1) -- Runs successfully ;WITH cte AS ( SELECT Email, COUNT(*) Count_ FROM #TEMP GROUP BY Email HAVING COUNT(*) > 1) SELECT Email FROM cte
I am wanting to understand why this may be. What is the issue with the query using the derived table?