Re: numbering a column according to group

Thanks Dave,

I'll give them a try.

Leigh

"Dave Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote in message news:<m17Pa.677$Gx4.471_at_newssvr16.news.prodigy.com>...
> "Dave Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote in message
> news:%W6Pa.675$Ic4.603_at_newssvr16.news.prodigy.com...
> >
> > "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
> >
> >
> >
>
> 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
Received on Thu Jul 10 2003 - 15:03:40 CDT