File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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

SQL: Retrieve duplicate records

Phebe Wilson

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

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

Joined: Apr 14, 2004
Posts: 10336

Something like this should work:

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Gareth Faulkner

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

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

Phebe Wilson

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

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

Joined: Jan 10, 2002
Posts: 60752

"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.

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.