Here's how:
/* 1. Query heading */
SELECT
* /* Remember to never use SELECT * and write out the field names */
FROM
/* 2. Table with duplicates */
SHIPMENT_ORDERS
WHERE
EXISTS (
SELECT
NULL
FROM
/* 3. Table with duplicates, with an alias */
SHIPMENT_ORDERS b
WHERE
/* 4. Join each field with *itself*. These are fields that could be
Primary Keys */
b.[shipment] = SHIPMENT_ORDERS.[shipment]
AND b.[purchase_order] = SHIPMENT_ORDERS.[purchase_order]
AND b.[item_sku] = SHIPMENT_ORDERS.[item_sku]
GROUP BY
/* 5. I must GROUP BY these fields because of the HAVING
clause and because these are the possible PK */
b.[shipment], b.[purchase_order], b.[item_sku]
HAVING
/* 6. This is the determining factor. We can control our
output from here. In this case, we want to pick records
where the ID is less than the MAX ID */
SHIPMENT_ORDERS.[id] < MAX(b.[id])
)
for details, check http://www.15seconds.com/issue/011009.htm
Dharmendar Kumar
RealMagnet - Email made easy
www.realmagnet.com
-----Original Message-----
From: Jeff [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 24, 2003 2:11 PM
To: SQL
Subject: This should be so easy, but I'm getting stumped...(duplicate
entries)
Pseudocode:
"delete every entry in MyTable where MyTable.Email is duplicated"
Trying to take a list with possible duplicate entries and remove all the
duplicates except one so that all are left is a single instance of an email
address, regardless of capitalization
Table: EmailAddresses
Column: Email
That's all. It sounds deceptively easy, doesn't it? Lol...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF
community.
http://affiliates.macromedia.com/b.asp?id=2439&p=go/st_aff1
Message: http://www.houseoffusion.com/lists.cfm?link=i:6:1676
Archives: http://www.houseoffusion.com/lists.cfm?link=t:6
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:>
