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, 'a@b.com'),
(2, 'c@d.com'),
(3, 'a@b.com')
-- 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?
;
in front of the statement. Make sure you end every statement with it: sqlblog.org/2009/09/03/… – a_horse_with_no_name Dec 20 '19 at 11:52