MIME-Version: 1.0
References: <e4688faf-de51-93ef-2db-f2acacbd658@appl-ecosys.com>
In-Reply-To: <e4688faf-de51-93ef-2db-f2acacbd658@appl-ecosys.com>
From: Muhammad Usman Khan <usman.k@bitnine.net>
Date: Thu, 12 Sep 2024 09:06:00 +0500
Message-ID: <CAPnRvGs1+uqWe1ny0jMJapYMNR+i=jDvWRL4YwsgsUdAQFSNGg@mail.gmail.com>
Subject: Re: Removing duplicate rows in table
To: Rich Shepard <rshepard@appl-ecosys.com>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000022df220621e43ad2"
Archived-At: <https://www.postgresql.org/message-id/CAPnRvGs1%2BuqWe1ny0jMJapYMNR%2Bi%3DjDvWRL4YwsgsUdAQFSNGg%40mail.gmail.com>
Precedence: bulk

--00000000000022df220621e43ad2
Content-Type: text/plain; charset="UTF-8"

Hi,
You can try the following CTE which removes all the identical rows and only
leave single row

WITH CTE AS (
  SELECT ctid, ROW_NUMBER() OVER (PARTITION BY proj_nbr, proj_name,
start_date, end_date, description, notes ORDER BY proj_nbr) AS rn
  FROM projects
  WHERE proj_nbr = '4242.02'
)
DELETE FROM projects
WHERE ctid IN (
  SELECT ctid FROM CTE WHERE rn > 1
);


On Tue, 10 Sept 2024 at 20:07, Rich Shepard <rshepard@appl-ecosys.com>
wrote:

> I've no idea how I entered multiple, identical rows in a table but I want
> to
> delete all but one of these rows.
>
> Here's an example:
>
> bustrac=# select * from projects where proj_nbr = '4242.01';
>   proj_nbr |   proj_name    | start_date |  end_date  |  description  |
> notes
>
> ----------+----------------+------------+------------+---------------+-------
>   4242.01  | Expert witness | 2008-10-15 | 2008-10-28 | Consol Energy |
>   4242.01  | Expert witness | 2008-10-15 | 2008-10-28 | Consol Energy |
>   4242.01  | Expert witness | 2008-10-15 | 2008-10-28 | Consol Energy |
>   4242.01  | Expert witness | 2008-10-15 | 2008-10-28 | Consol Energy |
> (4 rows)
>
> How do I clean this up so there's only a single row for this project
> number?
>
> TIA,
>
> Rich
>
>
>

--00000000000022df220621e43ad2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div dir=3D"ltr" class=3D"gmail_signature" data-smart=
mail=3D"gmail_signature"><div dir=3D"ltr"><div>Hi,<br>You can try the follo=
wing CTE which removes all the identical rows and only leave single row<br>=
<br>WITH CTE AS (<br>=C2=A0 SELECT ctid, ROW_NUMBER() OVER (PARTITION BY pr=
oj_nbr, proj_name, start_date, end_date, description, notes ORDER BY proj_n=
br) AS rn<br>=C2=A0 FROM projects<br>=C2=A0 WHERE proj_nbr =3D &#39;4242.02=
&#39;<br>)<br>DELETE FROM projects<br>WHERE ctid IN (<br>=C2=A0 SELECT ctid=
 FROM CTE WHERE rn &gt; 1<br>);<br></div></div></div></div><br></div><br><d=
iv class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, 10 S=
ept 2024 at 20:07, Rich Shepard &lt;<a href=3D"mailto:rshepard@appl-ecosys.=
com">rshepard@appl-ecosys.com</a>&gt; wrote:<br></div><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex">I&#39;ve no idea how I entered multiple, ident=
ical rows in a table but I want to<br>
delete all but one of these rows.<br>
<br>
Here&#39;s an example:<br>
<br>
bustrac=3D# select * from projects where proj_nbr =3D &#39;4242.01&#39;;<br=
>
=C2=A0 proj_nbr |=C2=A0 =C2=A0proj_name=C2=A0 =C2=A0 | start_date |=C2=A0 e=
nd_date=C2=A0 |=C2=A0 description=C2=A0 | notes <br>
----------+----------------+------------+------------+---------------+-----=
--<br>
=C2=A0 4242.01=C2=A0 | Expert witness | 2008-10-15 | 2008-10-28 | Consol En=
ergy |<br>
=C2=A0 4242.01=C2=A0 | Expert witness | 2008-10-15 | 2008-10-28 | Consol En=
ergy |<br>
=C2=A0 4242.01=C2=A0 | Expert witness | 2008-10-15 | 2008-10-28 | Consol En=
ergy |<br>
=C2=A0 4242.01=C2=A0 | Expert witness | 2008-10-15 | 2008-10-28 | Consol En=
ergy | <br>
(4 rows)<br>
<br>
How do I clean this up so there&#39;s only a single row for this project nu=
mber?<br>
<br>
TIA,<br>
<br>
Rich<br>
<br>
<br>
</blockquote></div>

--00000000000022df220621e43ad2--