EXPERT RESPONSE
Because we ultimately want two SerialNo values on the same row of the
final result set, we'll need a self-join. That is, we'll
join two copies of the table, t1 and t2. The join conditions will be
matching Status, matching Loc_ID, and the SerialNo in the t1 row
being less than or equal to the SerialNo in the t2 row:
select t1.Loc_ID
, t1.SerialNo as s1
, t2.SerialNo as s2
from yourtable as t1
inner
join yourtable as t2
on t1.Status = t2.Status
and t1.Loc_ID = t2.Loc_ID
and t1.SerialNo <= t2.SerialNo
where t1.Status = 'A'
order
by t1.Loc_ID
, t1.SerialNo
, t2.SerialNo
This produces a lot of results, too many to list. For example,
the row with t1.SerialNo 03 will produce the following rows in the self-join:
Loc_ID s1 s2
NY 03 03
NY 03 06
NY 03 07
NY 03 09
NY 03 10
Notice that 03 is matched to every Status 'A' SerialNo that is
greater than or equal to 03. This is a typical theta join.
Now let's add a condition. We want there to be no Status 'B'
SerialNo in between the t1.SerialNo and the t2.SerialNo in our
self-join.
select t1.Loc_ID
, t1.SerialNo as s1
, t2.SerialNo as s2
from yourtable as t1
inner
join yourtable as t2
on t1.Status = t2.Status
and t1.Loc_ID = t2.Loc_ID
and t1.SerialNo <= t2.SerialNo
where t1.Status = 'A'
and not exists
( select 1
from yourtable
where Loc_ID = t1.Loc_ID
and SerialNo between t1.SerialNo
and t2.SerialNo
and Status = 'B'
)
order
by t1.Loc_ID
, t1.SerialNo
, t2.SerialNo
This produces a much smaller result set, so we can list it all here:
Loc_ID s1 s2
NY 01 01
NY 01 02
NY 01 03
NY 02 02
NY 02 03
NY 03 03
NY 06 06
NY 06 07
NY 07 07
NY 09 09
NY 09 10
NY 10 10
Notice that there is no Status 'B' in between each pair of
Status 'A' SerialNos. In particular, all the rows for t1.SerialNo 03
were eliminated except one.
Please proceed to
Runs of sequential numbers (part 2 of 2).
|
