Re: update records to have occurance number

[Cachique <cachique@xxxxxxxxx>]

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 <mlybarger@xxxxxxxxx> 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!