How to select only duplicate records?

Question

Here's the table in sql server.. and below is what I want to do.

TABLE:

Area (column 1)
-----
Oregon
California
California
California
Oregon
Washington
Idaho
Washington

I want ALL the cities that are duplicates to be returned, but not the distinct ones. Just the duplicates. Is there a select statement that lets me return only duplicates?

Answer 1

Select State FROM Area
GROUP BY State
Having COUNT(*) > 1

Try this

Update the query with your own column and table names

Answer 2

While the GROUP BY .. HAVING approach is probably better here, this case - "more than one" - can also be answered with a JOIN assuming that there is a column (or set of columns) that form a Key. The requirement of a Key is that it must uniquely identify a record.

SELECT DISTINCT a1.State
FROM AREA a1
JOIN AREA a2
  ON a1.AreaId != a2.AreaId  -- assume there is a Key to join on
  AND a1.State = a2.State    -- and such that different Areas with same State