• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL: Retrieve duplicate records

 
Phebe Wilson
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If select distinct removes duplicates, How do you do you do just the opposite?
For example the table contains
11111
12345
12345
12345
22222
33333
99999
99999

I only want to retrieve 12345, 99999 all others should not show up in results. Can this be done?
Can this be done?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Something like this should work:
 
Gareth Faulkner
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How about this?

select col_1
from table_1
group by col_1
having count(*) > 1

Gareth
 
Phebe Wilson
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Gareth, that worked. Paul, the where clause didn't take.
Thank you very much!!! Problem solved.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Doh! I should test code before posting - a lesson I never seem to learn. Of course it needs to be having, not where. Well spotted Gareth Faulkner.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65129
92
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"M N",

There aren't may rules that you need to worry about here on the Ranch, but one that we take very seriously regards the use of proper names. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic