how can I delete double entries of different column

with other form i have table like

rowsid     source_row   same_as_row 

row1       1                18    

row2       3                 18

row3       18                1

row4       18                3

i want only elimate the rows3 and row4 :

row3       18                1

row4       18                3

which query! to delete query row3 and开发者_开发知识库 row4 .help

---

DELETE FROM table 
GROUP BY sourcerow
HAVING COUNT(*) > 1 

It should do it, assuming you're only looking for duplicate values in sourcerow column

---

select 
    rowsid, source_row, same_as_row
from tablename t1
where not exists 
(
    select * from tablename t2
    where t2.source_row = t1.same_as_row and t2.same_as_row = t1.source_row and t1.rowsid > t2.rowsid)
)

---

EDIT : Well blow me down (previous answer deleted)

Since double nesting works, this is the way I'd do it (even though part of it is highly redundant from a logical standpoint)

DELETE
  yourTable
FROM
  yourTable
INNER JOIN
  (SELECT * FROM (SELECT same_as_row FROM yourTable GROUP BY same_as_row)) as lookup
    ON lookup.same_as_row = yourTable.source_row
WHERE
    source_row > same_as_row

---

DELETE FROM table GROUP BY rowsid, source_row, same_as_row HAVING COUNT(*) > 1