RE: How do I check for partial duplicates?

Not the most elegant I'm sure but I'm tired and it's late here

again wth Kevin's

All disclaimers attached to a delete command sent out ............

DELETE FROM mem_info mi
WHERE EXISTS (SELECT ''

              FROM mem_info mi2
                          WHERE mi.mem_id = mi2.mem_id
                          AND   mi.mem_name = mi2.mem_name
                          AND   mi.mem_time < mi2.mem_time
                          AND   mi.ROWID    != mi2.ROWID)

-----Original Message-----
Sent: 15 August 2001 23:27
To: Multiple recipients of list ORACLE-L

All disclaimers attached to a delete command sent out ............

Try this

delete from mem_info
where (mem_id, mem_name, mem_time) in (select mem_id, mem_name, min(mem_time) from mem_info group by mem_id, mem_name)

-----Original Message-----
Sent: Wednesday, August 15, 2001 5:08 PM To: Multiple recipients of list ORACLE-L

Thanks!

Now I need to delete the records that have the earlier MEM_TIME. How would I do that?

Chris

Kevin Lange wrote:
>
> select mem_id, mem_name from mem_info group by mem_id, mem_name having
> count(*) > 1
>
> -----Original Message-----
> Sent: Wednesday, August 15, 2001 3:48 PM
> To: Multiple recipients of list ORACLE-L
>
> We have a table that may have partial duplicate rows. What select
> statement do I write to return the partial duplicates?
>
> table_name = MEM_INFO
>
> MEM_ID
> MEM_NAME
> MEM_TIME
> MEM_LAST
>
> I am concerned about two rows having the same MEM_ID and MEM_NAME.
> Having the same MEM_TIME and MEM_LAST is okay.
>
> Thanks,
>
> Chris
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Chris Rezek
> INET: crezek_at_dotclick.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
> --
> Author: Kevin Lange
> INET: kgel_at_ppoone.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
-- 
Author: Chris Rezek
  INET: crezek_at_dotclick.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: Kevin Lange
  INET: kgel_at_ppoone.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: iain.nicoll_at_calanais.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).