0

The following code works and creates a temporary table with a sequence number which is restarted for every new name:

with results as (select row_number() over (partition by name order BY name) as mytid,name from telephn_table)
select * from results order by name

My objective however is to insert the new sequence number permanently into the telephone table. How do I transfer the new sequence number from the results table to the telephone table? I have come across the following for MySql but was not able to convert it to Postgresql.

MySQL: Add sequence column based on another field

Can anyone help?

Community
  • 1
  • 1
george
  • 1

2 Answers2

3

If memory serves, row_number() returns the number within its own partition. In other words, row_number() over (partition by name order BY name) would return 1 for each row except duplicates. You likely want rank() over (order by name) instead.

After a long discussion:

update telephn_table
set sid = rows.new_sid
from (select pkey,
             row_number() over (partition BY name) as new_sid,
             name
      from telephn_table
      ) as rows
where rows.pkey = telephn_table.pkey;
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • @Denis Thank you for the answer but unfortunately it did not address my question which was "How do I transfer the new sequence number from the results table to the telephone table?". As I stated in my OP the code I listed works to my satisfaction, I just don't know how to copy/transfer the seq no. into the telephone table from the results table. – george May 18 '11 at 02:08
  • Oh, sorry. I thought you knew how to do that part... Just update the table: `update yourtable set tid = rows.tid from (select ...) as rows where rows.id = yourtable.id;` – Denis de Bernardy May 18 '11 at 02:13
  • @Denis Thanks again. If I understood you correctly (and please keep in mind I am a postgresql beginner) this is what I came up with: – george May 18 '11 at 02:56
  • update telephn_table set sid = rows.mytid from (with results as (select rank() over (partition by name order BY name) as mytid,name from telephn_table) select * from results order by name) as rows where rows.name = telephn_table.name; – george May 18 '11 at 02:56
  • This results in the sid column having 1 in all rows. Note that changing from row_number() to rank() gave identical results. What am I doing wrong? – george May 18 '11 at 02:58
  • update telephn_table set sid = rows.mytid from (with results as (select rank() over (order BY name) as mytid,name from telephn_table) select * from results order by name) as rows where rows.tid = telephn_table.sid; – george May 18 '11 at 04:12
  • Probably more like this: `update telephn_table set sid = rows.new_sid from (select sid, rank() over (order BY name) as new_sid, name from telephn_table) as rows where rows.sid = telephn_table.sid;`. Btw: you really shouldn't be bothering to re-number your primary key. Doing so defeats the whole point in using a surrogate key in the first place. – Denis de Bernardy May 18 '11 at 09:54
  • @Denis. Thanks Denis, looks better but not there yet. The following table tells you story: RESULT SHOULD BE "AW";180;1 "AW";180;1 "AY";179;2 "AY";179;1 "AZ";178;3 "AZ";178;1 "BA";290;4 "BA";290;1 "BA";362;4 "BA";362;2 "BB";77;6 "BB";77;1 "BB";174;6 "BB";174;2 "BC";385;8 "BC";385;1 "BC";357;8 "BC";357;2 "BC";56;8 "BC";56;3 "BD";56;11 "BD";56;1 "BD";140;11 "BD";140;2 "BE";357;13 "BE";357;1 "BE";385;13 "BE";385;2 "BE";423;13 "BE";423;3 – george May 18 '11 at 12:00
  • Sorry Denis, the table formatting is awful. Should have two columns headed RESULTS and SHOULD BE. Essentially I am looking for a sequential numbering scheme within name. Each new name should start again from 1. Two entries for the same name should be numbered 1 and 2, and so forth. – george May 18 '11 at 12:14
  • In that case, `(partition by name)` was correct, as was using `row_number()` instead of `rank()`. But my above suggested query is incorrect: you need a primary key, else you won't be able to update your table properly! And then adjust the update query to join on `rows.pkey = telephn_table.pkey`. Also, no offense but it would help if you read the postgresql tutorial before asking any further questions: http://www.postgresql.org/docs/9.0/static/tutorial.html. SO is not a place to crowd-source consulting work. – Denis de Bernardy May 18 '11 at 16:34
  • Thank you Denis for your help and advice and absolutely no offence taken – george May 18 '11 at 19:34
0

THIS WORKS! (See my OP link to a previous MySql link. In Postgresql it works without need for a temporary table)

alter table telephn_table add column tid integer default 0; UPDATE telephn_table set tid=(SELECT count(*)+1 from telephn_table t where t.sid < telephn_table.sid and telephn_table.name=t.name)

george
  • 1