Re: Using RANK in an update

Mike C wrote:
> I was trying to fill in blanks in a number field:
>
> CREATE TABLE test (a number, b number) ;
>
> insert into test values (1,1);
> insert into test values (2,2);
> insert into test values (3,4);
> insert into test values (4,6);
>
> I am looking for 4 to become 3 and 6 to become 4.
>
> When I tried:
>
> UPDATE test t1 set b= (SELECT rank() OVER (ORDER BY a) FROM test t2
> where
> t1.a=t2.a);
> I get 1 for everything for column b. I assume it is processing the
> rank 4 times (0ne at a time) and gets 1 because of the join.
>
> I got around this by doing the following, which works:
>
> CREATE TABLE test2 as
> SELECT a, rank() OVER (ORDER BY a) b FROM test;
>
> TRUNCATE TABLE test;
>
> INSERT INTO test select * from test2;
>
>
> I am curious though, if there is a way to make the update directly
> using RANK?
>
> I tried UPDATE test t1 set b= rank() OVER (ORDER BY a) and that
> doesn't work either.

One method that does not involve dropping the existing table, creating a new table, or TRUNCATING the existing table: What we are attempting to accomplish:
SELECT
  A,
  B,
  RANK() OVER (ORDER BY A,B) RANKING
FROM
  TEST; A B RANKING

1  1     1
2  2     2
3  4     3
4  6     4

Once the RANKING column contains the correct values, we can slide the values into column B, then drop the RANKING column.

ALTER TABLE
  TEST
ADD (
  RANKING NUMBER); Putting the values into the RANKING column is a bit challenging, unless we use inline views:
UPDATE
  TEST T1
SET
  RANKING=(
    SELECT
      RANKING
    FROM

      (SELECT
        A,
        B,
        RANK() OVER (ORDER BY A,B) RANKING
      FROM
        TEST) T2
    WHERE
      T1.A=T2.A
      AND T1.B=T2.B);

The inner-most inline view retrieves the RANK, and the values of A and B. The outer inline view strips out all but the one RANKING value that is of interest for the row being updated. This is an expensive operation if the table contains many rows.

SELECT
  *
FROM
  TEST; A B RANKING

1  1     1
2  2     2
3  4     3
4  6     4

UPDATE
  TEST
SET
  B=RANKING; ALTER TABLE
  TEST
DROP COLUMN
  RANKING; SELECT
  *
FROM
  TEST; A B
1 1
2 2
3 3
4 4

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Dec 21 2006 - 19:19:31 CST