I have a table structure which has 2 columns:
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.