Re: How to add and populate a new column?

> John Verbil wrote:
>
> 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.
>
> Are you sure you want to do this? This appears to be a pretty
> significant denormalization, and would mean that every time you
> insert/update/delete a row, you'll have to go through the entire table
> and repopulate that column. That's a lot of work if the table is big.
> You may simply want to create a cursor (or two) that can return rows to
> you in forward (or reverse) order, using ROWNUM, if necessary, to give
> you a ranking.
>
> --
> John Verbil
> U S WEST Communications
> Information Technologies
> jverbil_at_uswest.com
> (303) 896-0916John,
 

I agree with John, I'd recommend a view. My first cut didn't work. I did come up with this:

create view rank1 as
select customer_id, -quantity rev_order
from customers
group by -quantity, customer_id;

• You can not use ORDER BY with CREATE VIEW
• If quantity, customer_id do not make a unique set
• you may lose data

create view rank2 as
select rownum rank, customer_id, -rev_order quantity from rank1;

Seems a bit awkward and probably slow on large tables. Good Luck, Ron Received on Mon Dec 23 1996 - 00:00:00 CST