Delete duplicate rows from table

I have unique keys id keys in my table but I have a column with duplicate values? how do I get rid of those, while preserving only one of them like this :

Duplicate records :

id  | name   | surname |
1   | test   | one     |
2   | test   | two     |
3   | test3  | three   |
4   | test7  | four    |
5   | test   | five    |
6   | test11 | eleven  |

Without duplicates :

id  | name   | surname |
1   | test   | one     |
3   | test3  | three   |
4   | test7  | four    |
6   | test11 | eleven  |

I've googled this but it seems not to be working :

DELETE  ct1
FROM    mytable ct1
        , mytable ct2
WHERE   ct1.name = ct2.name 
        AND ct1.id < ct2.id 

E开发者_C百科RROR:  syntax error at or near "ct1"
LINE 1: DELETE  ct1
                ^

********** Error **********

I'm using postgres database.

You can try this running multiple times:

delete from mytable where id in (
    select max(id)
      from mytable
     group by name
    having count(1) > 1
);

Where multiple times equals the maximum number of repetitions you have in name column.

Otherwise, you can try this more complex query:

delete from mytable where id in (
    select id from mytable
    except 
    (
    select min(id)
      from mytable
     group by name
    having count(1) > 1
    union all
    select min(id)
      from mytable
     group by name
    having count(1) = 1
    )
);

Running this query one time only should delete all you need. Haven't tried it though...

Using Rank, actually I'm not totally sure about the syntax because I'm not that good at PostgreSQL, this is just a hint anyway (anybody's correction will be appreciated):

DELETE FROM mytable
WHERE id NOT IN
(
   SELECT x.id FROM
   (
      SELECT id, RANK() OVER (PARTITION BY name ORDER BY id ASC) AS r
      FROM mytable
   ) x
   WHERE x.r = 1
)