I need to get the maximum and minimum of the serial numbers with available status (A) or sold status (B) from the following table.
SerialNo Loc_ID Status 01 NY A 02 NY A 03 NY A 04 NY B 05 NY B 06 NY A 07 NY A 08 NY B 09 NY A 10 NY A
I want the range of available (A) serial numbers for a specific location, like this:
Location SerNo_From SerNo_To NY 01 03 NY 06 07 NY 09 10
I want the same for the sold serial numbers in a different query. Please help me to build these queries.
Requires Free Membership to View
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).
This was first published in December 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation