Re: Oracle Analytic Function - Suggestion/Tips needed

From: Michael Dinh <mdinh235_at_gmail.com>
Date: Thu, 18 Oct 2012 05:34:13 -0700
Message-ID: <CAENwkM7_fxB1eMxZhbb9nHj5jwWK_i8Lnbe9z=0jLu=3omjh2A_at_mail.gmail.com>

Will this work? Here is what I think is a representative of the data. SQL> select * from t order by 3,1;

N D_NAME SEQ
- ---------- ----------

A AA                100
B B1                106
B B2                106
B B3                106
C c1                116
B B3                121
B B2                121
B B1                121
D D1                131

9 rows selected.

SQL> select name, row_number() over (partition by seq order by name) rk from t;

N RK
- ----------

A          1
B          1
B          2
B          3
C          1
B          1
B          2
B          3
D          1

9 rows selected.

SQL> On Thu, Oct 18, 2012 at 4:44 AM, <Christopher.Taylor2_at_parallon.net> wrote:

> Version 10.2.0.4
>
> I've been going round and round with this.
>
> Here's a sample of my data and what I need out of it.
>
> NAME....DETAILED_NAME....START_DATE________.....END_DATE_________
> A.......Aaaaaaaaaaaaa....10/17/12 00:00:00......10/17/12 00:00:05
> B.......Bbbbbbbbbbbb1....10/17/12 00:00:06......10/17/12 00:00:10
> B.......Bbbbbbbbbbbb2....10/17/12 00:00:06......10/17/12 00:00:15
> B.......Bbbbbbbbbbbb3....10/17/12 00:00:06......10/17/12 00:00:12
> C.......Cccccccccccc1....10/17/12 00:00:16......10/17/12 00:00:20
> B.......Bbbbbbbbbbbb1....10/17/12 00:00:21......10/17/12 00:00:30
> B.......Bbbbbbbbbbbb2....10/17/12 00:00:21......10/17/12 00:00:30
> B.......Bbbbbbbbbbbb3....10/17/12 00:00:21......10/17/12 00:00:28
> D.......Dddddddddddd1....10/17/12 00:00:31......10/17/12 00:00:40
>
> I need to be able to number (rank) the As,Bs,Cs,Ds having the rank start
> over for each group that appears more than once so that it looks like this:
>
> NAME....RANK
> A.........1.
> B.........1.
> B.........2.
> B.........3.
> C.........1.
> B.........1.
> B.........2.
> B.........3.
> D.........1.
>
> Basically I need the RANK to start over at 1 for the 4th B and I cannot
> get my partitioning to work out right.
>
> Any thoughts?
>
> Chris
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 18 2012 - 14:34:13 CEST