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@xxxxxxxxx>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 > > > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l