Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Remove Duplicate Rows

 
Srinivasa Raghavan
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there any possible way to remove duplicate records using a sql query ..
And also without using the built in column rowid ?
Ex :
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ehm...you don't have any duplicate rows in your example.

SQL's way to remove duplicates is to use the "distinct" function.
 
Srinivasa Raghavan
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic