Am 06.02.24 um 00:32 schrieb David G. Johnston:
> On Mon, Feb 5, 2024 at 4:09 PM David Gauthier <dfgpostgres@gmail.com>
> wrote:
>
>
> I want the result to be just 2 recs, one for each dog.
>
>
> My present goto link for this question:
>
> https://harshitjain.home.blog/2019/06/17/postgresql-how-to-delete-duplicate-rows-in-postgresql/
>
> David J.
>
postgres=# select * from dogs;
dog
------
dog1
dog1
dog2
dog2
dog2
dog3
(6 rows)
postgres=# select ctid, dog, row_number() over (partition by dog) from
dogs ;
ctid | dog | row_number
-------+------+------------
(0,1) | dog1 | 1
(0,2) | dog1 | 2
(0,3) | dog2 | 1
(0,4) | dog2 | 2
(0,5) | dog2 | 3
(0,6) | dog3 | 1
(6 rows)
postgres=# with ct as (select ctid, dog, row_number() over (partition by
dog) from dogs) delete from dogs where ctid in (select ctid from ct
where row_number != 1) ;
DELETE 3
postgres=# select * from dogs;
dog
------
dog1
dog2
dog3
(3 rows)
postgres=#
Regards, Andreas
--
Andreas Kretschmer
CYBERTEC PostgreSQL Services and Support