Select every 2nd, 4th row and so on from same column based on specific material

In 1 packing got 2 material and item sequence is 00010 and 00020. What I need is if I input material from item sequence 00010 in where statement which is ‘CB016’ , I can list out all the item sequence 00020.

Table Data

Packing         ItemSeq ItemCate    Material    TargetQty   MinQty
1000009654      10      P           CB016       1            0
1000009654      20      I           10000015991 48           0
1000012548      10      P           CB016       1            0
1000012548      20      I           10000009495 48           0
1000012564      10      P           CB016       1            0
1000012564      20      I           10000009517 48           0
1000007961      10      P           CB017       1            0
1000007961      20      I           10000003423 10000        0
1000007962      10      P           CB017       1            0
1000007962      20      I           10000003424 10000        0

Expected Output

Packing         ItemSeq ItemCate    Material    TargetQty   MinQty
1000009654      20      I           10000015991 48           0
1000012548      20      I           10000009495 48           0
1000012564      20      I           10000009517 48           0

Answer

Window functions are not required here. You need the 20 rows for with there is a 10 row in the same group.

SELECT *
FROM yourdata item20
WHERE ItemSeq = 20
AND EXISTS (
    SELECT 1
    FROM yourdata item10
    WHERE item10.Packing = item20.packing
    AND ItemSeq = 10
    AND Material = 'CB016' -- insert material name here
)

DB Fiddle

Advertisement

Answer