This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Finding duplicates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Finding duplicates" Watch "Finding duplicates" New topic
Author

Finding duplicates

Anand Loni
Ranch Hand

Joined: Jan 20, 2006
Posts: 150
Hi,

I want to find the duplicate records from the table. My table structure is : 1st column is auto generated number, second column is listname and third column is data. I want to find out the duplicates from table with same listname and data.

I am confused with this. Can anybody help me?

Regards,
Anand


~ Anand,
SCJP 1.5, SCWCD 1.5
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

try this query , It will fetch only those records from table where listname and data is being repeated. but this may not solve your problem completely because it will exclude those value which are not being repeated as part of group (LISTNAME, DATA)



SELECT LISTNAME, DATA, COUNT(*) FROM MY_TABLE GROOUP BY LISTNAME, DATA HAVING COUNT(*) > 1


Thanks,
Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Muhammad Saifuddin
Ranch Hand

Joined: Dec 06, 2005
Posts: 1321

Originally posted by Shailesh Chandra:
SELECT LISTNAME, DATA, COUNT(*) FROM MY_TABLE GROOUP BY LISTNAME, DATA HAVING COUNT(*) > 1


just post to make a little correction in query,
remove the extra the "O" in GROUP BY clause.


Saifuddin..
[Blog][Linkedin] How To Ask Questions On JavaRanch My OpenSource
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Finding duplicates
 
Similar Threads
java data structures
Getting data from database into a collection
Once again JAVA-ORACLE
Database testing best practices/tools
join Hibernate query problem (Small problem)