Why does this query work with a CTE, but not with a derived table? [closed]

Question

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?

Answer 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.