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: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 10 Sep 2024 11:16:15 -0400
Message-ID: <CANzqJaDetd_6G0cdj-oivxXet3bwk_NFm96=B9s7RMWjXo9VAA@mail.gmail.com>
Subject: Re: Removing duplicate rows in table
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000007f28130621c55b3b"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDetd_6G0cdj-oivxXet3bwk_NFm96%3DB9s7RMWjXo9VAA%40mail.gmail.com>
Precedence: bulk

--0000000000007f28130621c55b3b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Sep 10, 2024 at 11:07=E2=80=AFAM 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=3D# select * from projects where proj_nbr =3D '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?
>

https://www.postgresqltutorial.com/postgresql-window-function/postgresql-ro=
w_number/

I'd use row_number to delete records where row_number > 1.

--=20
Death to America, and butter sauce.
Iraq lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Sep 10, 2024 at 11:07=E2=80=AFAM =
Rich Shepard &lt;<a href=3D"mailto:rshepard@appl-ecosys.com">rshepard@appl-=
ecosys.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex">I&#39;ve no idea how I entered multipl=
e, identical 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></blockquote><div><br></div><div><a href=3D"https://www.postgresql=
tutorial.com/postgresql-window-function/postgresql-row_number/">https://www=
.postgresqltutorial.com/postgresql-window-function/postgresql-row_number/</=
a><br></div><div>=C2=A0</div><div>I&#39;d use row_number to delete records =
where row_number &gt; 1.</div></div><div><br></div><span class=3D"gmail_sig=
nature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><di=
v dir=3D"ltr">Death to America, and butter sauce.<div>Iraq lobster!</div></=
div></div></div>

--0000000000007f28130621c55b3b--