Re: numbering a column according to group

"Dave Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote in message news:GT6Pa.673$2u4.437_at_newssvr16.news.prodigy.com...
> "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
>
>

Correction:

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
where b.cust_no = a.cust_no
and b.order_item = a.order_item
group by b.cust_no, b.order_item, b.order_date order by b.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
where b.cust_no = a.cust_no
and b.order_item = a.order_item
group by b.cust_no, b.order_item
order by b.order_date) c
where c.order_date = a.order_date)

Cheers,
Dave Received on Thu Jul 10 2003 - 00:35:55 CDT