[SOLVED] Renumbering a column in postgresql based on sorted values in that column

Renumbering a column in postgresql based on sorted values in that column

Edit: I am using postgresql v8.3

I have a table that contains a column we can call column A.

Column A is populated, for our purposes, with arbitrary positive integers.

I want to renumber column A from 1 to N based on ordering the records of the table by column A ascending. (SELECT * FROM table ORDER BY A ASC;)

Is there a simple way to accomplish this without the need of building a postgresql function?

Example:

(Before: A: 3,10,20,100,487,1,6) (After: A: 2,4,5,6,7,1,3)

Solution

Use the rank() (or dense_rank() ) WINDOW-functions (available since PG-8.4):

create table aaa
        ( id serial not null primary key
        , num integer not null
        , rnk integer not null default 0
        );
insert into aaa(num) values( 3) , (10) , (20) , (100) , (487) , (1) , (6)
        ;

UPDATE aaa
SET rnk = w.rnk
FROM (
        SELECT id
        , rank() OVER (order by num ASC) AS rnk
        FROM aaa
        ) w
WHERE w.id = aaa.id;

SELECT * FROM aaa
ORDER BY id
        ;

Results:

CREATE TABLE
INSERT 0 7
UPDATE 7
 id | num | rnk 
----+-----+-----
  1 |   3 |   2
  2 |  10 |   4
  3 |  20 |   5
  4 | 100 |   6
  5 | 487 |   7
  6 |   1 |   1
  7 |   6 |   3
(7 rows)

IF window functions are not available, you could still count the number of rows before any row:

UPDATE aaa
SET rnk = w.rnk
FROM (  SELECT a0.id AS id
        , COUNT(*) AS rnk
        FROM aaa a0
        JOIN aaa a1 ON a1.num <= a0.num
        GROUP BY a0.id
        ) w
WHERE w.id = aaa.id;

SELECT * FROM aaa
ORDER BY id
        ;

Or the same with a scalar subquery:

UPDATE aaa a0
SET rnk =
        ( SELECT COUNT(*)
        FROM aaa a1
        WHERE a1.num <= a0.num
        )
        ;