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?

TGH · Accepted Answer · 2013-07-14 04:04:49Z

22

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

Try this

Update the query with your own column and table names

answered Jul 14, 2013 at 4:04

TGH

39.4k13 gold badges106 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2246674 · Accepted Answer · 2013-07-14 04:10:39Z

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

@NISAR Then an appropriate Key is not being used (State can't be used as a key because it does not uniquely identify a record).
Thanks .. but your code take 93% query cost relative to this code takes 7% below.... "Select State FROM Area GROUP BY State Having COUNT(*) > 1"

Srinivasan · Accepted Answer · 2018-07-10 11:55:32Z

-1

select count(area),area_id from [Table_Name] group by area_id having count(area) > 1

answered Jul 10, 2018 at 11:55

Srinivasan

3031 gold badge6 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to select only duplicate records?

3 Answers 3

Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Related