Re: How to add and populate a new column?

From: Stephan Witt <witt_at_beusen.de>
Date: 1996/12/13
Message-ID: <32B1AAA4.1CFBAE39@beusen.de>#1/1

Z. Martinez wrote:
>
> I need to add a new column, called rank, in a table called customers.
> The "rank" column depends on another column called quantity.
> So, the row that has the highest "quantity" value gets a "rank" value
> of 1, and the lowest quantity value gets the last rank.
>
> Can anybody tell me how to add the new column and populate it.
>
> Thank you for any information
>
> Please reply to zlm101_at_psu.edu

Try that:
SQL> alter table rank add rank number;
SQL> desc rank;

 Name                            Null?    Typ
 ------------------------------- -------- ----
 QUANTITY                                 NUMBER
 RANK                                     NUMBER

SQL> create or replace view real_rank as select QUANTITY, rowid id from rank
group by QUANTITY, rowid ;
SQL> select * from real_rank where rownum < 10;

QUANTITY ID

-------- ------------------
    4967 00026E27.0034.0005
    4968 00026E27.0033.0005
    4969 00026E27.0032.0005
    4970 00026E26.0016.0005
    4971 00026E27.001B.0005
    4972 00026E27.003B.0005
    4973 00026E27.002E.0005

    4974 00026E26.0042.0005
    4975 00026E26.0142.0005
SQL> create table T_rank as select id, rownum rank from real_rank; SQL> update rank set rank = (select rank from T_rank where rank.rowid = T_rank.id) where exists (
select rank
from T_rank where rank.rowid = T_rank.id) ;
SQL> drop table T_rank;

But, it will be a hack anyway. Try to avoid such environment. Maybe, you can do what you want in another fashion? What if two have equal quantity? Do you need the rank information physically? Perhaps the view real_rank is already the solution? Like "select quantity, rownum rank from real_rank".

Hope it helps, Stephan

---

<stephan.witt_at_beusen.de>  | "beusen" Software+Systeme GmbH
fon: +49 30 549932-62     | Landsberger Allee 392
fax: +49 30 549932-21     | 12681 Berlin, Germany
---------------------------------------------------------------

Received on Fri Dec 13 1996 - 00:00:00 CST