0

Is there any way to find non distinct records in a table? I have a table which could have exact same records. Table contain more than 10 million records. 

ID Name
1  hello
1  hello
2  world
2  world
3  yikes

I want to select single copy of all those records which are appearing more than once. I want this result from above table.

ID Name
1  hello
2  world
abdul sammad
  • 141
  • 1
  • 2
  • 7
  • Please be more specific. Include more details about your table. Otherwise the answers will be like `SELECT * FROM yourTable` – ryanyuyu Jun 03 '15 at 19:38
  • @ryanyuyu Thanks! Is my question clear to you now? – abdul sammad Jun 03 '15 at 19:52
  • Yes that's a much clearer question. This question is a possible duplicate of [Finding duplicate rows in SQL Server](http://stackoverflow.com/questions/2112618/finding-duplicate-rows-in-sql-server) – ryanyuyu Jun 03 '15 at 19:54
  • are you sure? my table has no unique column like ID column in that question. There are exact replica of records in my table having same value of each column so i would have to group all the columns of the table @ryanyuyu – abdul sammad Jun 03 '15 at 19:59

1 Answers1

3

Just GROUP BY on more than one column. Then use HAVING to exclude groups that only have one row. 

SELECT id, name
FROM table
GROUP BY id, name
HAVING COUNT(*) > 1
ryanyuyu
  • 6,366
  • 10
  • 48
  • 53