update records to have occurance number

From: Mark Lybarger <mlybarger(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: update records to have occurance number
Date: 2016-10-25 15:06:26
Message-ID: CAKM-gmJXa90rKwiQ7Z4VXyBzaHAONzT7s1Z+aAvsHiFJ_budLw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I want to update a table to have the value of the occurrence number. For
instance, I have the below table. I want to update the number column to
increment the count of last name occurrences, so that it looks like this:

first last 1
second last 2
third last 3
first other 1
next other 2

Here's my simple table:

create table person ( fname text, lname text, number integer);

insert into person (fname, lname) values ('first', 'last');
insert into person (fname, lname) values ('second', 'last');
insert into person (fname, lname) values ('third', 'last');

insert into person (fname, lname) values ('first', 'other');
insert into person (fname, lname) values ('next', 'other');

How would I issue an update statement to update the number column?

thanks!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message t.dalpozzo@gmail.com 2016-10-25 15:08:26 streaming replication and WAL
Previous Message Adrian Klaver 2016-10-24 21:30:09 Re: configure PostgreSQL with PERL: Perl version 5.8 or later is required, but this is .