Simplify TSQL by Getting Ids that matched or first in the row number if not matched-CodePudding

I have this example where Id1 serves as the group and Id2 is an Id unique in the group.

If Id2 is the same as Id1, I want to get its row as a representative of the whole group.

If there's no Id1 that matched to Id2, then I want to get the first row based on the order of ascending Id2.

This is how I did it but I just wonder how I can simplify the tsql:

WITH cte AS
(
    SELECT 'A' AS Id1, '1' AS Id2, 'DSFSF' AS _detail
    UNION ALL
    SELECT 'A' AS Id1, '2' AS Id2, 'ASDF' AS _detail
    UNION ALL
    SELECT 'A' AS Id1, 'A' AS Id2, '434242' AS _detail
    UNION ALL
    SELECT 'B' AS Id1, '1' AS Id2, 'gsreew' AS _detail
    UNION ALL
    SELECT 'B' AS Id1, '2' AS Id2, 'werw' AS _detail
    UNION ALL
    SELECT 'B' AS Id1, '3' AS Id2, '67575' AS _detail
),
cte2 AS
(
    SELECT 
        *, 
        ROW_NUMBER() OVER (PARTITION BY Id1 ORDER BY Id2) AS rn,
        CASE 
            WHEN Id1 = Id2 THEN 1 ELSE 0 
        END AS _matchedid
    FROM
        cte
),
cte3 AS
(
    SELECT
        Id1, SUM(_matchedid) AS _matched
    FROM
        cte2
    GROUP BY
        Id1
)
SELECT *
FROM cte2 a
INNER JOIN cte3 b ON a.Id1 = b.Id1
WHERE b._matched = 1 AND a.Id1 = a.Id2

UNION ALL

SELECT *
FROM cte2 a
INNER JOIN cte3 b ON a.Id1 = b.Id1
WHERE b._matched = 0 AND a.rn = 1

CodePudding user response：

One idea uses TOP (1) WITH TIES and ROW_NUMBER with a conditional ORDER BY:

SELECT TOP (1) WITH TIES *
FROM CTE
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID1 ORDER BY CASE ID1 WHEN ID2 THEN NULL ELSE ID2 END);

Note that as your column ID2 is a varchar, then a value like '10' will have a lower value than something like '2'.

CodePudding user response：

You can use a conditional row-numbering solution for this

WITH cte AS
(
    SELECT 'A' AS Id1, '1' AS Id2, 'DSFSF' AS _detail
    UNION ALL
    SELECT 'A' AS Id1, '2' AS Id2, 'ASDF' AS _detail
    UNION ALL
    SELECT 'A' AS Id1, 'A' AS Id2, '434242' AS _detail
    UNION ALL
    SELECT 'B' AS Id1, '1' AS Id2, 'gsreew' AS _detail
    UNION ALL
    SELECT 'B' AS Id1, '2' AS Id2, 'werw' AS _detail
    UNION ALL
    SELECT 'B' AS Id1, '3' AS Id2, '67575' AS _detail
),
cteWithRn AS
(
    SELECT *
      ROW_NUMBER() OVER (PARTITION BY Id1, CASE WHEN Id1 = Id2 THEN 0 ELSE 1 END, Id2) AS rn
    FROM cte
)
SELECT *
FROM cteWithRn
WHERE rn = 1;