{"id":462,"date":"2010-02-08T12:02:09","date_gmt":"2010-02-08T12:02:09","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=462"},"modified":"2014-06-24T18:04:43","modified_gmt":"2014-06-24T18:04:43","slug":"oracle-query-eliminate-duplicate-but-one-using-rowid","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/oracle-query-eliminate-duplicate-but-one-using-rowid","title":{"rendered":"Oracle query-eliminate duplicate but one using rowid"},"content":{"rendered":"

Yesterday a friend came across an oracle query problem:<\/p>\n

Consider below table:<\/p>\n

cid\tcname....\tcdata
\n1\tx\t\txxxx
\n1\tx\t\txxxx ..
\n2\txzzz\t\tfjnd
\n3\ta\t\tevddd<\/code><\/p>\n

Now the problem was cid is repeated and cdata column is having some extra characters but data is considered same (eg. xxxx & xxxx..).
\nso when he does
\nSELECT cid,cname....cdata FROM TABLENAME ....;
\n<\/code>
\nHe kept on getting duplicated records which intern he wanted to have only one per duplicated cid.
\nIt’s not by the way of table structure but by the way of query he wanted to have results.<\/p>\n

I suggested him:
\nSELECT cid,cname....cdata FROM TABLENAME GROUP BY cid;
\n<\/code>
\nAs 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.
\nThis is Oracle \ud83d\ude42<\/p>\n

Well later I came back to my place and recollected something called ROWID of Oracle, may be that can help!!<\/p>\n

Then I created similar table in MySQL to “stimulate” Oracle rowID:<\/p>\n

rowId\tcid\tcname ....\tcdata
\n1\t1\tx\t\txxxx
\n2\t1\tx\t\txxxx ..
\n3\t2\txzzz\t\tfjnd
\n4\t3\ta\t\tevddd<\/code><\/p>\n

And finally I got the solution as:<\/p>\n

select cid,cname….cdata from tablename where rowID in (select min(rowID) from tablename group by cid);<\/p>\n

So finally I decided to give it a try on Oracle and it worked \ud83d\ude42<\/p>\n

select * from TABLENAME WHERE rowid in (select min(rowid) from TABLENAME group by DUPLICATECOLUMN);
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"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…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,6],"tags":[247,94,248,426],"class_list":["post-462","post","type-post","status-publish","format-standard","category-mysql","category-technical","tag-elliminatae-duplicate-in-oracle","tag-query","tag-rowid","tag-technical"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t