Re: numbering a column according to group

"Leigh" <leighlhg_at_hotmail.com> wrote in message news:b8cf9ab1.0307092035.78ce99d8_at_posting.google.com...
> HELP! Anyone SQL gurus out there please help me with a query update to
> a table:
>
> I need to update a column in a table sequentially base on the date and
> group by the customer number and the order item with data as such:
>
> cust_no, order_item, order_date column_to_update
> 111 AAA 01/20/02 1
> 111 AAA 08/20/02 2
> 111 AAA 12/20/02 3
> 333 AAA 01/15/02 1
> 333 AAA 03/26/02 2
> 555 BBB 01/06/02 1
> 555 BBB 03/06/02 2
> 555 BBB 04/26/02 3
> 555 BBB 05/26/02 4
>
> I only have oracle 8.1.7 Standard edition: so no analytical feature.
>
> Thanks in advanced.

Here's the sql for dense rank (i.e. rows with equal value receive the same rank and the ranking number does not have any gap):

update your_table a
set column_to_update =
(select rownum from
(select order_date
from your_table b
group by b.cust_no, b.order_item, b.order_date having b.cust_no = a.cust_no
and b.order_item = a.order_item
order by order_date) c
where c.order_date = a.order_date)

Here's the sql for non-dense rank (i.e. rows with equal value receive the same rank but if two rows receive the same rank, the rank number will skip):

update your_table a
set column_to_update =
(select rownum from
(select order_date
from your_table b
group by b.cust_no, b.order_item
having b.cust_no = a.cust_no
and b.order_item = a.order_item
order by order_date) c
where c.order_date = a.order_date)

Cheers,
Dave Received on Thu Jul 10 2003 - 00:32:22 CDT