Re: update records to have occurance number

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(at)gmail(dot)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!
>