File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL:  Retrieve duplicate records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL:  Retrieve duplicate records" Watch "SQL:  Retrieve duplicate records" New topic
Author

SQL: Retrieve duplicate records

Phebe Wilson
Greenhorn

Joined: Sep 30, 2005
Posts: 2
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

Joined: Apr 14, 2004
Posts: 10336

Something like this should work:


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Gareth Faulkner
Greenhorn

Joined: Oct 29, 2004
Posts: 22
How about this?

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

Gareth
Phebe Wilson
Greenhorn

Joined: Sep 30, 2005
Posts: 2
Thank you Gareth, that worked. Paul, the where clause didn't take.
Thank you very much!!! Problem solved.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Jan 10, 2002
Posts: 60752
    
  65

"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


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
 
jQuery in Action, 2nd edition
 
subject: SQL: Retrieve duplicate records
 
Similar Threads
Compare string content in 2 vectors
java.sql.SQLException: ORA-01722: invalid number
Random 5-Digit number
how to display data in dropdown box from database using java code
substring ints.