SQL Server 2017

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


(No column name)


Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64) 
	Jun 22 2022 18:20:15 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE MCancel
  ( Acc INT NOT NULL, 
    TranType INT NOT NULL,
    PosCancelID INT NOT NULL
  ) ;
INSERT INTO MCancel
  (Acc, TranType, PosCancelID)
VALUES
  (100, 1, 2),
  (808, 1, 5),
  (808, 1, 4),
  (808, 2, 5),
  (808, 2, 4),
  (813, 2, 3),
  (813, 2, 5),
  (813, 4, 3),
  (813, 4, 4),
  (813, 4, 5)  ;
10 rows affected

SELECT *
FROM MCancel ;


Acc
TranType
PosCancelID


100
1
2

808
1
5

808
1
4

808
2
5

808
2
4

813
2
3

813
2
5

813
4
3

813
4
4

813
4
5


    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 ;


Acc
TranType
posCancelID


100
1
2

808
1
4

808
2
5

813
2
3


    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 ;


Acc
TranType
posCancelID


100
1
2

808
1
4

808
2
5

813
2
3

813
4
4