If I have three duplicate rows, how can I keep the first row and remove other two rows?

Question

Inversely, if I had three unique rows, how would a command be used to achieve this?

Answer 1

Just use "distinct". If you want to delete remaining rows use instructions from this topic How can I remove duplicate rows?

Answer 2

The below query should do for deleting duplicate records

delete from table 
where rowid not in (
select min(rowid) from table group by unique_id
)

Ok, for sql server try the query below

delete from table 
    where %%physloc%% not in (
    select min(%%physloc%%) from table group by unique_id
    )

The above query for sql server might be slow becas I believe it will do a full table scan.

Answer 3

So you have a table with 3 identical rows and some more ...

CREATE TABLE people (id,name) AS
          SELECT 42, 'Arthur Dent'
UNION ALL SELECT 43, 'Zaphod Beeblebrox'
UNION ALL SELECT 42, 'Arthur Dent'
UNION ALL SELECT 44, 'Tricia McMillan'
UNION ALL SELECT 42, 'Arthur Dent'
;

There is hardly a way other than to create a brand-new table with de-duped rows, and make sure it gets the name of the original table. Here goes:

ALTER TABLE people RENAME TO people_with_dupes;
CREATE TABLE people AS
SELECT DISTINCT * FROM people_with_dupes;

The people table is now de-duped:

SELECT * FROM people ORDER BY id;

id|name
42|Arthur Dent
43|Zaphod Beeblebrox
44|Tricia McMillan