0

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?

2
  • 6
    Additionally, don't put the ; 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
  • 1
    Please include the error message when asking a question. It does provide valuable information for the community. Thanks. – John K. N. Dec 20 '19 at 15:03
1

You are missing an alias for the derived table.

Books online - Emphasis mine

When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.

Not the answer you're looking for? Browse other questions tagged or ask your own question.