Re: SQL how to question

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