aspose file tools*
The moose likes JDBC and the fly likes Remove Duplicate Rows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Remove Duplicate Rows" Watch "Remove Duplicate Rows" New topic
Author

Remove Duplicate Rows

Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
Is there any possible way to remove duplicate records using a sql query ..
And also without using the built in column rowid ?
Ex :


Thanks & regards, Srini
MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Ehm...you don't have any duplicate rows in your example.

SQL's way to remove duplicates is to use the "distinct" function.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
Originally posted by Paul Sturrock:
Ehm...you don't have any duplicate rows in your example.

SQL's way to remove duplicates is to use the "distinct" function.


Sorry i shd have writtern in other way
In the example there are two rows with empno 1
empno x y z
1 A AA A1
1 B BB B1

I need the first row in the o/p ?
Is this possible ?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You have a problem because your set of results is not really a set. Relational Databases work on the assumption that tables are sets of tuples - i.e. every row is unique (because its defined by a primary key). This isn't true for the results you show. You want to show a result which is data for an empno - but empno is not unique. Hence you can't do it (and there's no bit of SQL you could use to get round this in any safe way).

If you have a table that is showing the type of data you list, I'd suspect that this is a result of a model that has not been properely normalized. If you can, I'd possibly rethink the DDL for this entity.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Remove Duplicate Rows