SQL - How to get elements after and before the searched one?

Question

I want to query my table and specify some offset to get rows before and after my found one.

E.g. getting this result set (Specifying offset of 2):

• data 1
• data 2 --> offset: 2 --> so this row is added to my result set as well
• data 3 --> offset: 2 --> so this row is added to my result set as well
• data 4 --> this row is meeting my query condition
• data 5 --> offset: 2 --> so this row is added to my result set as well
• data 6 --> offset: 2 --> so this row is added to my result set as well
• data 7

I have tried LAG/LEAD but this only gives me the possibility to get one column of the next row, I would need a feature which basically adds the offset rows to the result set as well.

Edit: My sample data looks like this: I got data which stores a cardboard_number, timestamp (says when the cardboard was scanned) and a productionline_number. So basically in my table I have following Columns:

• ID (auto-increment, int)
• Cardboard_Number (varchar)
• DateTime (datetime)
• ProductionLine_Number (int)

Basically, I want to search for a Cardboard_Number, and get the specified number of offsets before and after the one.

So to update my preceding example:

• 2 "1234" 2024-03-23 17:01:01 1
• 4 "123" 2024-03-24 17:01:01 1
• 5 "135" 2024-03-25 09:01:01 2
• 7 "234" 2024-03-25 11:01:01 1
• 9 "134" 2024-03-25 13:01:01 2
• 10 "12345" 2024-03-25 14:01:01 1
• 11 "12346" 2024-03-25 17:01:01 2

So, when specifying e.g. offset 1 and searching for Cardboard_Number "134", I want to get the rows with IDs 5, 9 and 11, as I want to know which cardboard_number was scanned before and after my searched one per ProductionLine_Number

Edit 2: In some cases, a Cardboard_Number can occur multiple times in one ProductionLine AND occur in other ProductionLines as well.

I have created a dbfiddle showing some duplicated cardboards. In this fiddle, when searching for 'WDL-005943998-1' (with an offset of e.g. 1), I would expect IDs 2, 4, 5, 6, 7 from Line1 as result and IDs 9, 10, 11, 12 from Line2, ultimately resulting in a result set of 2, 4, 5, 6, 7, 9, 10, 11, 12.

To conclude, the result set should contain all Cardboards with the searched number and additionally the "offset rows" which were scanned before and after the searched Cardboard at the line the Cardboard was used.

Answer 1

You just need to sum over a range of rows for this:

select id, Cardboard_Number, date_Time, ProductionLine_Number
from (
  select id, Cardboard_Number, date_Time, ProductionLine_Number,
    sum(Cardboard_Number='spL1ml82N4o') over (
      partition by ProductionLine_Number
      order by date_Time, id
      rows between 2 preceding and 2 following
    ) matches
  from table1
) table1_with_matches
where matches

This will read the entire table; if that's not good enough and you have a (Cardboard_Number) index, you could limit it to only production lines that have the number you are looking for:

select id, Cardboard_Number, date_Time, ProductionLine_Number
from (
  select p2.id, p2.Cardboard_Number, p2.date_Time, p2.ProductionLine_Number,
    sum(p2.Cardboard_Number='spL1ml82N4o') over (
      partition by p2.ProductionLine_Number
      order by p2.date_Time, p2.id
      rows between 2 preceding and 2 following
    ) matches
  from table1 p1
  join table1 p2 using (ProductionLine_Number)
  where p1.Cardboard_Number='spL1ml82N4o'
) table1_with_matches
where matches

If that's still not good enough, you can also restrict to some time range of the matching row. How to do that depends on whether a Cardboard_Number can occur more than once for the same ProductionLine_Number.

Answer 2

Solution to your problem:

SET @offset = 1;
SET @cb_num = 'WDL-005943998-1'; #Replace it with any other Cardboard Number
WITH CTE as
(
  SELECT *,
         ROW_NUMBER() 
         OVER(PARTITION BY PRODUCTIONLINE_NUMBER ORDER BY date_TIME,id ASC) as RN
  FROM table1
  WHERE PRODUCTIONLINE_NUMBER IN (SELECT 
                             PRODUCTIONLINE_NUMBER 
                             FROM table1 
                             WHERE CARDBOARD_NUMBER = @cb_num)
)
SELECT id, Cardboard_Number
FROM CTE as co
WHERE @offset >= ANY (SELECT ABS(co.RN - ci.RN) 
                           FROM CTE as ci 
                           WHERE CARDBOARD_NUMBER = @cb_num 
                           AND co.PRODUCTIONLINE_NUMBER = ci.PRODUCTIONLINE_NUMBER)

dbfiddle link - https://dbfiddle.uk/L9ryjZ3J