Here is an example of the select using the old, reliable emp table that I populated with duplicates.
SQL> l
1 select e1.rowid,
2 e1.empno,
3 e1.ename
4* from emp e1
SQL> /
ROWID
EMPNO ENAME
------------------ ---------- ----------
AAABb5AAEAAAUIiAAA 7369 SMITH
AAABb5AAEAAAUIiAAB 7499 ALLEN
AAABb5AAEAAAUIiAAC 7521 WARD
AAABb5AAEAAAUIiAAD 7566 JONES
AAABb5AAEAAAUIiAAE 7654 MARTIN
AAABb5AAEAAAUIiAAF 7698 BLAKE
AAABb5AAEAAAUIiAAG 7782 CLARK
AAABb5AAEAAAUIiAAH 7788 SCOTT
AAABb5AAEAAAUIiAAI 7839 KING
AAABb5AAEAAAUIiAAJ 7844 TURNER
AAABb5AAEAAAUIiAAK 7876 ADAMS
AAABb5AAEAAAUIiAAL 7900 JAMES
AAABb5AAEAAAUIiAAM 7902 FORD
AAABb5AAEAAAUIiAAN 7934 MILLER
AAABb5AAEAAAUIiAAO 7369 SMITH
AAABb5AAEAAAUIiAAP 7499 ALLEN
AAABb5AAEAAAUIiAAQ 7521 WARD
AAABb5AAEAAAUIiAAR 7566 JONES
AAABb5AAEAAAUIiAAS 7654 MARTIN
AAABb5AAEAAAUIiAAT 7698 BLAKE
AAABb5AAEAAAUIiAAU 7782 CLARK
AAABb5AAEAAAUIiAAV 7788 SCOTT
AAABb5AAEAAAUIiAAW 7839 KING
AAABb5AAEAAAUIiAAX 7844 TURNER
AAABb5AAEAAAUIiAAY 7876 ADAMS
AAABb5AAEAAAUIiAAZ 7900 JAMES
AAABb5AAEAAAUIiAAa 7902 FORD
AAABb5AAEAAAUIiAAb 7934 MILLER
1 select e1.rowid,
2 e1.empno,
3 e1.ename
4 from emp e1,
5 (select empno, min(rowid)
min_rowid
6 from emp
7 group by empno)
e2
8 where e1.empno = e2.empno
9* and e1.rowid != e2.min_rowid
SQL> /
ROWID
EMPNO ENAME
------------------ ---------- ----------
AAABb5AAEAAAUIiAAO 7369 SMITH
AAABb5AAEAAAUIiAAP 7499 ALLEN
AAABb5AAEAAAUIiAAQ 7521 WARD
AAABb5AAEAAAUIiAAR 7566 JONES
AAABb5AAEAAAUIiAAS 7654 MARTIN
AAABb5AAEAAAUIiAAT 7698 BLAKE
AAABb5AAEAAAUIiAAU 7782 CLARK
AAABb5AAEAAAUIiAAV 7788 SCOTT
AAABb5AAEAAAUIiAAW 7839 KING
AAABb5AAEAAAUIiAAX 7844 TURNER
AAABb5AAEAAAUIiAAY 7876 ADAMS
AAABb5AAEAAAUIiAAZ 7900 JAMES
AAABb5AAEAAAUIiAAa 7902 FORD
AAABb5AAEAAAUIiAAb 7934 MILLER
"Bellow, Bambi" wrote:
Friends --One of my associates came up to me Friday with a question. It seemed easy
enough. I mean, I've been doing stuff like this for years. The question
was, "I have duplicate ids here, some with X field null, some without. I
want to get rid of all the duplicates where X field is not null." Fine.
Standard correlated subquery.delete from <tablename> a
where rowid not in (select min(rowid)
where pid=a.pid
and X is not null)
and X is not nullRight?
So, my associate says "what are you doing? you're going to go through the
table every single time for each record?" Why, yes, that is indeed what I'm
doing here. "Why don't you just open a cursor and delete that way?" says my
associate...select pid
from <tablename>
where X is not null
group by pid
having count(*) > 1
{
skip one
delete the rest
}"But," I tell my associate, "you still have to go to the table to get
information on which records to delete. You can't do this without a
correlated subquery."Yes, he assures me, there *has* to be a way.
OK. Maybe I've just been doing things the same way for too long. I'm
willing to cop to that. Can anybody out there come up with a way to do this
relatively normal operation without a correlated subquery?Bambi.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bellow, Bambi
INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
