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

do a self-join on the table:
select a.last_name, a.pid
from names a, names b
where a.last_name = b.last_name and a.rowid != b.rowid;

This is saying find me all entries where the last names are the same but the rows are different. Do an explain plan to make sure you're hitting the right index and the query is optimized.

Hank Griffioen <hank_at_daac.gsfc.nasa.gov> wrote in article <34201C6E.CF4C987B_at_daac.gsfc.nasa.gov>...
> 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***)
>
Received on Wed Sep 17 1997 - 00:00:00 CDT