Yesterday a friend came across an oracle query problem:

1. By using ROWID, Oracle Optimizer would select the fastest path, which is a direct route to a single row of data. The following code selects the most current record for every duplicate pair of col1 and col2.
2. Finding duplicate rows using the aggregate function To find duplicate rows from the fruits table, you first list the fruit name and color columns in both SELECT and GROUP BY clauses. Then you count the number of appearances each combination appears with the COUNT (.) function as shown below.
3. Oracle has a pseudo column called 'ROWID', which is unique for each row in a table. So we can use that to delete duplicate records. The above code will find only one ROWID for non-duplicate records. For duplicate records, it'll delete a record with lower ROWID. If there are more than two columns that make up unique key for a record, then you.

By specifying how many duplicate values we want to get in a sub-query, we can obtain non-duplicate or duplicate records from a table. Duplicates are those records that are duplicated twice or more in the table. Queries on this page use the Oracle Northwind database that was converted from the popular Access Northwind database.

Consider below table:

cid cname.... cdata
1 x xxxx
1 x xxxx ..
2 xzzz fjnd
3 a evddd

Now the problem was cid is repeated and cdata column is having some extra characters but data is considered same (eg. xxxx & xxxx..).
so when he does
SELECT cid,cname....cdata FROM TABLENAME ....;

He kept on getting duplicated records which intern he wanted to have only one per duplicated cid.
It’s not by the way of table structure but by the way of query he wanted to have results.

I suggested him:
SELECT cid,cname....cdata FROM TABLENAME GROUP BY cid;

As far as I know this works with MySQL, but it gave error as I came to know that I have to include all SELECTed variables in GROUP BY clause.
This is Oracle 🙂

Well later I came back to my place and recollected something called ROWID of Oracle, may be that can help!!

Then I created similar table in MySQL to “stimulate” Oracle rowID:

How To Eliminate Duplicate Rows In Oracle Without Using Rowid

rowId cid cname .... cdata
1 1 x xxxx
2 1 x xxxx ..
3 2 xzzz fjnd
4 3 a evddd

And finally I got the solution as:

select cid,cname….cdata from tablename where rowID in (select min(rowID) from tablename group by cid);

So finally I decided to give it a try on Oracle and it worked 🙂

select * from TABLENAME WHERE rowid in (select min(rowid) from TABLENAME group by DUPLICATECOLUMN);


You might also like::

See Full List On Wikihow.com