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


Related posts:

1. No More Spool Space Teradata Query Solution Have you ever come across a situation called NO MORE SPOOL...
2. Monty: Help Saving MySQL – The Oracle & EC We all are aware about the acquisition happened to MySQL;...
3. Stored procedure – Execute query if table or Column exists Well procedures mainly carried out working with information schema and...
4. Stored procedure to Find database objects This procedure lists available database objects under passed database name....