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.

"Leigh" <leighlhg (AT) hotmail (DOT) com> wrote

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

"Dave Hau" <davehau-no-spam-123 (AT) no-spam (DOT) netscape.net> wrote


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

"Dave Hau" <davehau-no-spam-123 (AT) no-spam (DOT) netscape.net> wrote

Correction for the case of non-dense rank:

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 min(rownum) from
(select order_date
from your_table b
where b.cust_no = a.cust_no
and b.order_item = a.order_item
order by b.order_date) c
where c.order_date = a.order_date)


Cheers,
Dave

Thanks Dave,

I'll give them a try.

Leigh

"Dave Hau" <davehau-no-spam-123 (AT) no-spam (DOT) netscape.net> wrote