How to select all duplicate rows except original one?

Question

Let's say I have a table

CREATE TABLE names (
    id SERIAL PRIMARY KEY,
    name CHARACTER VARYING
);

with data

id    name
-------------
1     John
2     John
3     John
4     Jane
5     Jane
6     Jane

I need to select all duplicate rows by name except the original one. So in this case I need the result to be this:

id    name
-------------
2     John
3     John
5     Jane
6     Jane

How do I do that in Postgresql?

Answer 1

You can use ROW_NUMBER() to identify the 'original' records and filter them out. Here is a method using a cte:

with Nums AS (SELECT id, 
                     name, 
                     ROW_NUMBER() over (PARTITION BY name ORDER BY ID ASC) RN
              FROM names)

SELECT *
FROM Nums
WHERE RN <> 1 --Filter out rows numbered 1, 'originals'

Answer 2

select * from names where not id in (select min(id) from names
group by name)