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)
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 <mlybarger@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:
firstlast 1
secondlast 2
thirdlast 3
firstother 1
nextother 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?