Perform ranking depend on category

Question

I Have a table looks like this:

RowNum    category     Rank4A     Rank4B
-------------------------------------------
1         A            
2         A
3         B
5         A
6         B
9         B

My requirement is based on the RowNum order, Make two new ranking columns depend on category. Rank4A works like the DENSERANK() by category = A, but if the row is for category B, it derives the latest appeared rank for category A order by RowNum. Rank4B have similar logic, but it orders by RowNum in DESC order. So the result would like this (W means this cell I don't care its value):

RowNum    category     Rank4A     Rank4B
-------------------------------------------
1         A            1          W
2         A            2          W
3         B            2          3
5         A            3          2
6         B            W          2
9         B            W          1

One more additional requirement is that CROSS APPLY or CURSOR is not allowed due to dataset being large. Any neat solutions?

Edit: Also no CTE (due to MAX 32767 limit)

Answer 1

Giorgos Betsos' answer is better, please read it first.

Try this out. I believe each CTE is clear enough to show the steps.

IF OBJECT_ID('tempdb..#Data') IS NOT NULL
    DROP TABLE #Data

CREATE TABLE #Data (
    RowNum INT,
    Category CHAR(1))

INSERT INTO #Data (
    RowNum,
    Category)
VALUES
    (1, 'A'),
    (2, 'A'),
    (3, 'B'),
    (5, 'A'),
    (6, 'B'),
    (9, 'B')

;WITH AscendentDenseRanking AS 
(
    SELECT
        D.RowNum,
        D.Category,
        AscendentDenseRanking = DENSE_RANK() OVER (ORDER BY D.Rownum ASC)
    FROM
        #Data AS D
    WHERE
        D.Category = 'A'
),
LaggedRankingA AS
(
    SELECT
        D.RowNum,
        AscendentDenseRankingA = MAX(A.AscendentDenseRanking)
    FROM
        #Data AS D
        INNER JOIN AscendentDenseRanking AS A ON D.RowNum > A.RowNum
    WHERE
        D.Category = 'B'
    GROUP BY
        D.RowNum
),
DescendantDenseRanking AS 
(
    SELECT
        D.RowNum,
        D.Category,
        DescendantDenseRanking = DENSE_RANK() OVER (ORDER BY D.Rownum DESC)
    FROM
        #Data AS D
    WHERE
        D.Category = 'B'
),
LaggedRankingB AS
(
    SELECT
        D.RowNum,
        AscendentDenseRankingB = MAX(A.DescendantDenseRanking)
    FROM
        #Data AS D
        INNER JOIN DescendantDenseRanking AS A ON D.RowNum < A.RowNum
    WHERE
        D.Category = 'A'
    GROUP BY
        D.RowNum
)
SELECT
    D.RowNum,
    D.Category,
    Rank4A = ISNULL(RA.AscendentDenseRanking, LA.AscendentDenseRankingA),
    Rank4B = ISNULL(RB.DescendantDenseRanking, LB.AscendentDenseRankingB)
FROM
    #Data AS D
    LEFT JOIN AscendentDenseRanking AS RA ON D.RowNum = RA.RowNum
    LEFT JOIN LaggedRankingA AS LA ON D.RowNum = LA.RowNum
    LEFT JOIN DescendantDenseRanking AS RB ON D.RowNum = RB.RowNum
    LEFT JOIN LaggedRankingB AS LB ON D.RowNum = LB.RowNum


/*
Results:

    RowNum      Category Rank4A               Rank4B
    ----------- -------- -------------------- --------------------
    1           A        1                    3
    2           A        2                    3
    3           B        2                    3
    5           A        3                    2
    6           B        3                    2
    9           B        3                    1
*/

This isn't a recursive CTE, so the limit 32k doesn't apply.

Answer 2

You can use the following query:

SELECT RowNum, category, 
       SUM(CASE 
              WHEN category = 'A' THEN 1 
              ELSE 0 
       END) OVER (ORDER BY RowNum) AS Rank4A,
       SUM(CASE 
              WHEN category = 'B' THEN 1 
              ELSE 0 
       END) OVER (ORDER BY RowNum DESC) AS Rank4B

FROM mytable
ORDER BY RowNum