MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1
(
    id int,
    Cardboard_Number varchar(100),
	date_Time datetime,
	ProductionLine_Number int
);
INSERT INTO table1 VALUES 
(2,'WDL-005943998-1','2024-02-29 17:13:50', 1),
(4,'spL1ml82N4o','2024-02-29 17:13:54', 1),
(5,'WDL-005943998-1','2024-03-01 09:44:42', 1),
(6,'WDL-005943998-1','2024-03-01 10:52:57', 1),
(7,'950024027237','2024-03-01 10:58:42', 1),
(9,'WDL-005943998-1','2024-03-01 13:58:43', 2),
(10,'WDL-005943998-1','2024-03-01 13:58:46', 2),
(11,'spL1ml82N4o','2024-03-01 14:09:43', 2),
(12,'WDL-005943998-1','2024-03-12 15:48:36', 2);

Select * from table1;

Records: 9  Duplicates: 0  Warnings: 0




id
Cardboard_Number
date_Time
ProductionLine_Number


2
WDL-005943998-1
2024-02-29 17:13:50
1

4
spL1ml82N4o
2024-02-29 17:13:54
1

5
WDL-005943998-1
2024-03-01 09:44:42
1

6
WDL-005943998-1
2024-03-01 10:52:57
1

7
950024027237
2024-03-01 10:58:42
1

9
WDL-005943998-1
2024-03-01 13:58:43
2

10
WDL-005943998-1
2024-03-01 13:58:46
2

11
spL1ml82N4o
2024-03-01 14:09:43
2

12
WDL-005943998-1
2024-03-12 15:48:36
2


SET @offset = 1;
SET @cb_num = 'WDL-005943998-1'; #For example
WITH CTE as
(
  SELECT *,
         ROW_NUMBER() 
         OVER(PARTITION BY PRODUCTIONLINE_NUMBER ORDER BY date_TIME ASC) as RN
  FROM table1
  WHERE PRODUCTIONLINE_NUMBER IN (SELECT 
                             PRODUCTIONLINE_NUMBER 
                             FROM table1 
                             WHERE CARDBOARD_NUMBER = @cb_num)
)
SELECT id
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)


id


2

4

5

6

7

9

10

11

12


SET @offset = 1;
SET @cb_num = 'spL1ml82N4o';
WITH CTE as
(
  SELECT *,
         ROW_NUMBER() 
         OVER(PARTITION BY PRODUCTIONLINE_NUMBER ORDER BY date_TIME ASC) as RN
  FROM table1
  WHERE PRODUCTIONLINE_NUMBER IN (SELECT 
                             PRODUCTIONLINE_NUMBER 
                             FROM table1 
                             WHERE CARDBOARD_NUMBER = @cb_num)
)
SELECT id
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)


id


2

4

5

10

11

12


SET @offset = 1;
SET @cb_num = '950024027237';
WITH CTE as
(
  SELECT *,
         ROW_NUMBER() 
         OVER(PARTITION BY PRODUCTIONLINE_NUMBER ORDER BY date_TIME ASC) as RN
  FROM table1
  WHERE PRODUCTIONLINE_NUMBER IN (SELECT 
                             PRODUCTIONLINE_NUMBER 
                             FROM table1 
                             WHERE CARDBOARD_NUMBER = @cb_num)
)
SELECT id
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)


id


6

7

id	Cardboard_Number	date_Time	ProductionLine_Number
2	WDL-005943998-1	2024-02-29 17:13:50	1
4	spL1ml82N4o	2024-02-29 17:13:54	1
5	WDL-005943998-1	2024-03-01 09:44:42	1
6	WDL-005943998-1	2024-03-01 10:52:57	1
7	950024027237	2024-03-01 10:58:42	1
9	WDL-005943998-1	2024-03-01 13:58:43	2
10	WDL-005943998-1	2024-03-01 13:58:46	2
11	spL1ml82N4o	2024-03-01 14:09:43	2
12	WDL-005943998-1	2024-03-12 15:48:36	2