Skip to Main Content
  • Questions

  • Real Time Scenarios & duplicate rows

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Srin.

Asked: July 29, 2016 - 2:12 am UTC

Last updated: July 29, 2016 - 2:07 pm UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

I have two questions

1.) Deleting Duplicate rows

DELETE FROM EMP1 WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP1 GROUP BY EMPNO);

the above command deletes the duplicate rows from a given table such as emp1 in this case, group by eliminates duplicates but im trying to understand what does min(rowid) does. please explain, I can even specify MAX(ROWID) and use the same code to delete but at that time what does MAX(ROWID) does?


2.) Where can i find oracle DBA real time scenarios please do let me know

and Chris said...

1. min(rowid) returns the first rowid for each empno. You could replace this with max if you want.

The point is you want just one row for each empno. For example if you have:

EMPNO Rowid
1     AAAAA
1     AAAAB
2     AAAAC
3     AAAAD
3     AAAAE


You want to remove one of the rows for empnos 1 and 3. Taking the min, grouped by empno gives you:

Rowid
AAAAA
AAAAC
AAAAD


The delete then removes rowids that aren't in this list (AAAAB & AAAAE). If you took the max, it would be rows AAAAA and AAAAD you removed instead.

For further discussion, see

https://blogs.oracle.com/sql/entry/how_to_find_and_delete

2. I don't understand. What are "oracle DBA real time scenarios"?

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.