[RESOLVED] Complicated (sort-of) duplicates query

[fuzzy1]

Hey All,

I've been struggling for a couple of days now with a duplicates issue which leaves me... well -- STUMPED😕

In a relationship table "contacts_orders" (id,contact_id,orders_id,date,deleted) it is acceptable -- desirable even --
for there to be TWO records with the SAME orders_id, and a DISTINCT contact_id (1 sellers_agent_id, 1 buyers_agent_id)
and also for there to be many instances of a single contact_id having been recorded for distinct orders(unique orders_id's.)

On the occasion where there are duplicates -- that is, the SAME contact_id recorded MORE THAN ONCE for a given orders_id --
HOW BEST MIGHT I CHECK FOR AND ELIMINATE THESE DUPES??

IS IT POSSIBLE IN A SINGLE QUERY(sub-query) STATEMENT?
Something like ...

DELETE FROM contacts_orders GROUP BY orders_id, contact_id HAVING count(*)>1 

[fuzzy1]

Okay, This Probably should be moved to the DATABASE forum,
but here's my solution, short & sweet on the chance it might
help some other poor soul. I knew that unique indexing either
order_id or contact_id was out of the question.
What I didn't know was that I could indext them in tandem-- like so...

ALTER IGNORE TABLE contacts_orders
ADD UNIQUE INDEX Buyer_SellerNoDupe (contact_id,orders_id)