Oracle query-eliminate duplicate but one using rowid

Yesterday a friend came across an oracle query problem:

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:

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);