Re: Oracle Analytic Function - Suggestion/Tips needed

From: Ghassan Salem <salem.ghassan_at_gmail.com>
Date: Thu, 18 Oct 2012 14:23:15 +0200
Message-ID: <CALEzEShNkPOsG_hN47LHgqwUO2m7JfUPskMXWTXfRSTNaiR8Lw_at_mail.gmail.com>

Chris,
you did not specify how to order your data, but it seems to be on start_date. The other thing missing is that if start_date for each group is the same. It seems so from your data, but it may not be always the case. If the above assumption is good, use something like this select a.*, row_number() over (partition by name,start_date order by .....) from ....

If the assumption is not true, you need to tell us how to find the 'groups'.

On Thu, Oct 18, 2012 at 1:44 PM, <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:23:15 CEST