Subselect fails, but as view it works

Question

I was hoping to run a single query and get results from that. However, MSSQL complains about a syntax error near the GROUP key word. I was hoping to do the following.

  SELECT COUNT(*) AS cnt,Field_2
    FROM (SELECT DISTINCT Field_1, Field_2 
            FROM Table_1)
GROUP BY Field_2 
  HAVING COUNT(*) > 1

Instead I had to create a view, View_1, with the query

SELECT DISTINCT Field_1, Field_2 
  FROM Table_1

And then do a

  SELECT COUNT(*) AS cnt, Field_2
    FROM View_1
GROUP BY Field_2 
  HAVING COUNT(*) > 1

The question is why, to my mind it is essentially the same SQL.

Note: Field, table and view names have been changed to protect the innocent. ;-)

Answer 1

SQL Server requires you to specify a table alias for a derived table/inline view:

  SELECT COUNT(*) AS cnt, 
         x.Field_2
    FROM (SELECT DISTINCT Field_1, Field_2 
            FROM Table_1) AS x
GROUP BY x.Field_2 
  HAVING COUNT(*) > 1