Here is a general query (assuming the table has at least an IDENTITY field
called [id]) Note that this does not require a temp table (and you should
not use a temp table for this at all):
/* you could change this to DELETE FROM to remove
these duplicates without a temp table */
SELECT *
FROM
/* table with duplicates */
myTable
WHERE EXISTS (
SELECT NULL
FROM
/* table with duplicate, aliased */
myTable b
WHERE
/* match each field with "itself"
this would be regarded as the Primary Key
*/
b.last_name = myTable.last_name
AND b.first_name = myTable.first_name
GROUP BY
/* we group by the PK fields because we have [id]
in an aggregate function
*/
b.last_name, b.first_name
HAVING
/* this is the determining factor */
MAX(b.id) > myTable.id
)
David L. Penton, MCP
"Mathematics is music for the mind, and Music is Mathematics for the
Soul." - J.S. Bach
[EMAIL PROTECTED]
-----Original Message-----
From: Chris E. Clark
I can find how to hide duplicate records in a report, but I want to
delete them. I have to sort by a field and look through my list, it's up
to 3065, to find duplicates -- that's too many to look through. How do I
Query to find and report duplicates.
--
========================================================================
There's More To Life Chris E. Clark
6917 Brookvale Rd. [EMAIL PROTECTED]
Ft. Worth, TX 76132 http://www.tmtl.com
Voice/Fax (817) 263-8685
Home Automation...because you don't have time.
Factory authorized Mastervoice dealer
Matt 6:33 "But seek first the kingdom of God and His righteousness,
and all these things shall be added to you." NKJV
========================================================================
-------------------------------------------------------------------------
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
