Re: bin fitting question ( a little different than examples on the web)

From: rjamya <rjamya_at_gmail.com>
Date: Thu, 29 Aug 2013 12:29:16 -0400
Message-ID: <CAGurbTPNmX_tAn82dCfx2Hq6DiqSaizbcm1NU4uetxsVj8dcWg_at_mail.gmail.com>

something like
with x as (select owner, object_name, row_number() over (partition by owner order by object_name) rn from dba_objects) select owner, mod(rn,50) as cnt, floor(rn/50)+1 as group_cnt from x
/

??

On Thu, Aug 29, 2013 at 11:47 AM, Dba DBA <oracledbaquestions_at_gmail.com>wrote:

> 11.2.0.3. I need to do some bin sorting. I saw Jonathan Lewis's page, the
> asktom discussion, and toms summary of it in Oracle Magazine. I need to do
> something a little different. I am trying to figure out how to do a certain
> piece of it.
> My initial hope was that I could use row_number,dense, or dense_rank and a
> window clause, but when I did this, I got errors and googled it. Window
> Clauses are not support with these functions.
>
> Is there a way to do this in the SQL Model clause? Or another way? If there
> is another way, can I call this from a model clause? I just started digging
> into the model clause. So I don't know it real well.
>
> Basically I need to:
>
> partition by a varchar field. (for test purposes the values are just
> A,B,C,D)
> With in each partition, I need to count from 1 to 50. For each cycle, I
> need to increase another counter.
>
> That probably doesn't make sense, so let me do it in pseudo-code
> This won't compile...
> declare
> cursor mySQL is
> select field1 from my bigtable;
> cnt number :=0;
> groupcnt number := 1;
> lastField1 mybigTable.field1%type;
> begin
> for i in mySQL loop
> if nvl(lastField1,0) = i.field1 then
> if cnt <= 50 then
> cnt := cnt+1;
> end if;
> else
> cnt := 1;
> groupcnt := group+1;
> end if;
> else
> cnt :=1
> groupcnt := 1;
> end if;
> end loop;
>
> There would be business logic in here. So basically the output would be
>
> Field 1 CNT GROUPCNT
> A 1 1
> A ... 50 1 -- CNT would have 2-49 as well
> A 1 2
> B 1 1
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Thu Aug 29 2013 - 18:29:16 CEST