0

I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows:

42, 1
43, 1
42, 2
43, 5
43, 6
42, 3

the output contains the client_id, and boundaries of the gaps, like this:

43, 2, 4

Note that there's no row with client_id=42 because there are no gaps in its data.

I've tried this for the core part of the query:

select 
    client_id, 
    order_no as start_order_no, 
    lead(order_no) over (order by client_id, order_no) as end_order_no

but that apparently doesn't do what I want, and I suspect it's because of client_id in the OVER part.

  • 2
    You probably meant partition by client_id order by order_no – mustaccio Nov 1 '18 at 18:41
0

You should use a CTE or a subquery to get the lead order_no first.

select client_id,
       order_no + 1 as c1,
       no - 1 c2
from   (select client_id, 
               order_no,
               lead(order_no) 
                   over (partition by client_id order by client_id, order_no) as no
from     tbl) ct
where  no is not null
and    no - order_no > 1;
with ct as
(
select   client_id, 
         order_no,
         lead(order_no) 
             over (partition by client_id order by client_id, order_no) as no
from     tbl
)
select client_id,
       order_no + 1 as c1,
       no - 1 c2
from   ct
where  no is not null
and    no - order_no > 1;
client_id | c1 | c2
--------: | -: | -:
       43 |  2 |  4

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.