Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Life without a correlated subquery

Re: Life without a correlated subquery

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 10 Nov 2003 09:34:25 -0800
Message-ID: <F001.005D63A7.20031110093425@fatcity.com>


It is a little convoluted, but you can use an inline query. It is not a correlated subquery, it may be more efficient, your mileage may vary, contents under pressure...

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 null
>
> Right?
>
> 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: bbellow_at_chi.navtech.com
>
> 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: ListGuru_at_fatcity.com (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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Fink
  INET: Daniel.Fink_at_Sun.COM

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: ListGuru_at_fatcity.com (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).
Received on Mon Nov 10 2003 - 11:34:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US