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

Home -> Community -> Usenet -> c.d.o.server -> Re: Very Simple SQL question ... hepl ? ...

Re: Very Simple SQL question ... hepl ? ...

From: Gary England <gengland_at_hiwaay.net>
Date: 1997/09/17
Message-ID: <342085EF.4234@hiwaay.net>#1/1

Hank Griffioen wrote:
>
> I have a table called names that has last_name and pid in
> the table. I am tryint to find a simple query that
> will all me to select all rows that have duplicated last names.
>
> For example
>
> last_name pid
> --------------------------
> smith 1
> smith 3
> jones 4
> griffin 5
> stevens 6
> stevens 7
>
> I want to do the opposite of distinct.
>
> something like ....
> select duplicate last_name pid from person.
>
> smith 1
> smith 3
> stevens 6
> stevens 7
>
> --
> Hank Griffioen CFII,MEII,AGI,IGI INTERNET:
> hank_at_daacdev1.nasa.gsfc.gov
> GSFC BLD 32 Code 902.2 fax 614-5304
> Greenbelt, MD 20770 Phone: 301-614-5488
> To order CD-ROMs and data URL = http://daac.gsfc.nasa.gov/
> NASA Goddard Flying Club URL = http://daac.gsfc.nasa.gov/NGFC/
> (***I do not speak for HSTX, NASA or NASA Goddard Flying Club***)

SELECT last_name, pid FROM table
WHERE last_name in (SELECT last_name

   FROM table
   GROUP BY last_name HAVING COUNT(*) > 1);

If last_name is a secondary key it will be VERY FAST.

Gary Received on Wed Sep 17 1997 - 00:00:00 CDT

Original text of this message

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