Home » SQL & PL/SQL » SQL & PL/SQL » min(rowid)
min(rowid) [message #254278] Thu, 26 July 2007 06:26 Go to next message
lijojoseph
Messages: 9
Registered: July 2007
Location: Mumbai
Junior Member
What is a rowid. I read that for deleting duplicate records the following command can be used

delete from emp where rowid not in(select min(rowid) from
emp group by empno);

what is this min(rowid) will select is it returns a single value whic will be common for unique records or something else?

how it is identifying the row id of unique records.
Re: min(rowid) [message #254280 is a reply to message #254278] Thu, 26 July 2007 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rowid is the physical address of the row.

Regards
Michel
Re: min(rowid) [message #254290 is a reply to message #254280] Thu, 26 July 2007 06:35 Go to previous messageGo to next message
lijojoseph
Messages: 9
Registered: July 2007
Location: Mumbai
Junior Member
can you please explain the above example
Re: min(rowid) [message #254292 is a reply to message #254290] Thu, 26 July 2007 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Subquery search all rows with same empid and keep only the ones with minimal address (rowid) then it delete the others.

Regards
Michel
Re: min(rowid) [message #254295 is a reply to message #254292] Thu, 26 July 2007 06:42 Go to previous messageGo to next message
lijojoseph
Messages: 9
Registered: July 2007
Location: Mumbai
Junior Member
Thanks Michel,

That means if there are 2 duplicate rows, then it will delete the second one (one with high rowid)
Re: min(rowid) [message #254301 is a reply to message #254295] Thu, 26 July 2007 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: min(rowid) [message #254302 is a reply to message #254295] Thu, 26 July 2007 06:48 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It will delete either one. There is no 'second' or 'first', unless you provide an explicit order by.
Indeed, the one with the higher rowid will be deleted, but this is not necessarily the one that was inserted last.
Previous Topic: Sytem Triggers at Database level
Next Topic: HOW TO GET FIRST DATE OF ENTERED DATE
Goto Forum:
  


Current Time: Fri Dec 09 17:13:06 CST 2016

Total time taken to generate the page: 0.23703 seconds