Finding the Min value for a subgroup, filtering out preceding Min values within the same parent group
Quite a hard problem. Here is a recursive solution:
WITH
rcte AS
( SELECT TOP (1)
Acc, TranType, posCancelID,
CAST('=' + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX)) AS IDs
FROM
MCancel
ORDER BY
Acc, TranType, posCancelID
UNION ALL
SELECT
Acc, TranType, posCancelID,
CAST(IDs + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX))
FROM
( SELECT
m.*,
r.IDs,
ROW_NUMBER() OVER (ORDER BY m.Acc, m. TranType, m.PosCancelID) AS rn
FROM
rcte AS r
JOIN MCancel AS m
ON (m.Acc = r.Acc AND m.TranType > r.TranType)
OR (m.Acc > r.Acc)
WHERE
r.IDs NOT LIKE ('%=' + CAST(m.posCancelID AS VARCHAR(20)) + '=%')
) AS mc
WHERE
rn = 1
)
SELECT Acc, TranType, posCancelID
FROM rcte
ORDER BY Acc, TranType ;
The solution assumes that a posCancelID
should not appear twice in the result set. If the requirements are that they should not appear twice in the same Acc
group, then the solution needs a slight adjustment:
WITH rcte AS
( SELECT
Acc, TranType, posCancelID,
CAST('=' + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX)) AS IDs
FROM
( SELECT
Acc, TranType, posCancelID,
ROW_NUMBER() OVER (PARTITION BY Acc ORDER BY TranType, PosCancelID) AS rnk
FROM MCancel
) AS f
WHERE rnk = 1
UNION ALL
SELECT
Acc, TranType, posCancelID,
CAST(IDs + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX))
FROM
( SELECT
m.*, r.IDs,
ROW_NUMBER() OVER (PARTITION BY m.Acc
ORDER BY m.TranType, m.PosCancelID) AS rn
FROM
rcte AS r
JOIN MCancel AS m
ON (m.Acc = r.Acc AND m.TranType > r.TranType)
WHERE
r.IDs NOT LIKE ('%=' + CAST(m.posCancelID AS VARCHAR(20)) + '=%')
) AS mc
WHERE rn = 1
)
SELECT Acc, TranType, posCancelID
FROM rcte
ORDER BY Acc, TranType ;
Both are tested at dbfiddle.uk
Using a CURSOR:
This solution uses a temporary table to store each calculation of minim value and filtering next one using data stored in this temporary table for the previous Acc values.
CREATE TABLE #tmp
(
Acc int,
TranType int,
PosCancelID int
);
GO
DECLARE @acc int,
@trantype int,
@poscancelid int;
DECLARE cur CURSOR FOR
SELECT Acc, TranType
FROM MCancel
GROUP BY Acc, TranType
ORDER BY Acc, TranType;
OPEN cur;
FETCH NEXT FROM cur INTO @acc, @trantype;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tmp
SELECT Acc, TranType, MIN(PosCancelID)
FROM MCancel
WHERE Acc = @acc
AND TranType = @trantype
-- filter any previous min value for same Acc
AND PosCancelID NOT IN (SELECT PosCancelID
FROM #tmp
WHERE Acc = MCancel.Acc)
GROUP BY Acc, TranType;
FETCH NEXT FROM cur INTO @acc, @trantype;
END
CLOSE cur;
DEALLOCATE cur;
SELECT * FROM #tmp;
Acc | TranType | PosCancelID
--: | -------: | ----------:
100 | 1 | 2
808 | 1 | 4
808 | 2 | 5
813 | 2 | 3
db<>fiddle here