Search Postgresql Archives

Re: update records to have occurance number

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux