Skip to Main Content
  • Questions

  • Real Time Scenarios & duplicate rows

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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"?

We're not taking comments currently, so please try again later if you want to add 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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.