Re: SQL how to question

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Here's a (partial) solution...

with numbered
 as (select clnt, ord, row_number() OVER (partition by clnt order by ord)
as rowNbr
     , dense_rank() OVER (order by clnt) grpNbr
     ,
     from mytest)
select clnt, ord
  , grpNbr  + rownbr / 5 as dossier
  , mod(rownbr - 1, 5) + 1  as order
from numbered;

But the question is what happens for "Client C"?  I suspect that would be a
dossier of 4...which the above wouldn't support.

May need a UDF that makes use of the scratchpad..

Or if on 7.3, make use of the LAG() function

Charles






On Thu, Apr 15, 2021 at 8:58 AM Dave <dfx1@xxxxxxxxxxxxxx> wrote:

> Hi,
>
> I was thinking I could do this with an OLAP function, but there's a
> particularity that has me stumped
>
> How can I get from this :
> Client A  Order x
> Client A  Order z
> Client A  Order a
> Client B  Order g
> Client B  Order h
> Client B  Order i
> Client B  Order l
> Client B  Order p
> Client B  Order m
>
> To this:
> Client A  Order x 1 1
> Client A  Order z 1 2
> Client A  Order a 1 3
> Client B  Order g 2 1
> Client B  Order h 2 2
> Client B  Order i 2 3
> Client B  Order l 2 4
> Client B  Order p 2 5
> Client B  Order m 3 1
>
> A dossier n° is assigned to each client  and a line number to each order.
> If there are more than 5 orders, a new dossier is created.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com