HI: You can use windows functions. https://www.postgresql.org/ docs/9.5/static/tutorial-window.html specifically row_number(). https://www.postgresql.org/ docs/9.5/static/tutorial-window.html
I'm assuming that your ordering is based on lname and then fname. The query for getting 'number' is sandbox=# select row_number() over (partition by lname order by fname, lname) number, fname, lname from person number | fname | lname --------+--------+------- 1 | first | last 2 | second | last 3 | third | last 1 | first | other 2 | next | other (5 rows) And combined with UPDATE FROM... https://www.postgresql.org/ docs/9.5/static/sql-update.html sandbox=# update person p set number = d.number from (select row_number() over (partition by lname order by fname, lname) number, fname, lname from person) d where p.fname = d.fname and p.lname = d.lname; UPDATE 5 Regards, Walte On Tue, Oct 25, 2016 at 12:06 PM, Mark Lybarger <mlybar...@gmail.com> wrote: > 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! >