Tukukkk

How do I get the minimum value for a group (Acc, TranType), but filtering out any minimum values used in preceding rows for the Acc group. Preceding rows would be defined as Acc asc, TranType asc

So with the given data set:

Acc  | TranType | PosCancelID

100     1               2

808     1               5

808     1               4

808     2               5

808     2               4<--To be filtered from min calc as it min for (808,1)

813     2               3

813     4               3<--To be filtered from min calc as it min for (813,2)

I should get:

Acc  | TranType | PosCancelID

100     1               2

808     1               4

808     2               5

813     2               3



SELECT ACC, TranType, min(maxPreceeding) as ActualCancelID 

FROM 

(

   SELECT ACC, TranType,  

          MAX(m.posCancelID) OVER (PARTITION BY m.ACC 

                                  ORDER BY m.TranType, m.posCancelID 

                                  ROWS UNBOUNDED PRECEDING) as maxPreceeding

    FROM MCancel as m

) AS x

GROUP BY ACC, TranType

The above query gives me almost what i want but isn't filtering for acc = 813. So I know there must be a better (actually applying a filter to remove previous minimum values) way.

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



    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

First CTE simply calculates minimum PosCancelID of each group of (Acc, TranType).

Second one, adds the minimum PosCancelID calculated of the previous group.

And finally it calculates again the minimum PosCancelID, avoiding the minimum of the previous group. Note I've used t1.PosCancelID <> ct2.LastMin, maybe you need a solution where t1.PosCancelID > ct2.LastMin.

WITH ct1 AS

(

    SELECT 

        Acc, TranType,

        MIN(PosCancelID)as MinP

    FROM

        MCancel

    GROUP BY 

        Acc, TranType

)

, ct2 AS

(

    SELECT 

        Acc, TranType, MinP,

        COALESCE(LAG(MinP) OVER (ORDER BY Acc, TranType), 0) LastMin

    FROM

        ct1

)

SELECT 

    t1.Acc, t1.TranType,

    MIN(PosCancelID) as MinP

FROM

    MCancel t1

JOIN

    ct2

    ON ct2.Acc = t1.Acc

    AND ct2.TranType = t1.TranType

WHERE

    t1.PosCancelID <> ct2.LastMin

GROUP BY

    t1.Acc, t1.TranType;

Acc | TranType | MinP

--: | -------: | ---:

100 |        1 |    2

808 |        1 |    4

808 |        2 |    5

813 |        2 |    3

db<>fiddle here