SQL Server complex Ranking

user_5300021 0
Viewed 514

I have this date 

DECLARE @tbl table (ID INT, Items VARCHAR(5))
INSERT INTO  @tbl VALUES
(1,'A'),(1,'A'),(1,'A'),(1,'B'),(1,'D'),(2,'A'),(2,'A'),(2,'B'),
(2,'B'),(2,'B'),(3,'A'),(3,'A'),(3,'B'),(3,'B'),(3,'C'),(3,'C'),
(3,'C'),(3,'E'),(3,'F')

What I want is In ID 1 there are 3 distinct items A,B, and C, all duplicate like A is ranked 1, B and C do not have duplicate so ranked 2 and 3 respectively IN ID 2 there are 2 distinct items A and B, A is 1 and the first next item B will be 1 until will get to the last value of item B that will be 2 etc

Current output

ID  Items   RN
1   A       1
1   A       2
1   A       3
1   B       1
1   D       1
2   A       1
2   A       2
2   B       1
2   B       2
2   B       3
3   A       1
3   A       2
3   B       1
3   B       2
3   C       1
3   C       2
3   C       3
3   E       1
3   F       1

Desired Output

enter image description here

My current query is

SELECT
*
,ROW_NUMBER()OVER(PARTITION BY  CAST(ID AS VARCHAR(2))+Items  ORDER BY  Items DESC)  AS RN
FROM @tbl
ORDER BY ID,Items
tsql sql-server
2 Answers

If I understand your logic correctly, next approach may help. The appraoch uses two separate CTEs to number unique and not-unique sequences.

Table:

DECLARE @tbl table (ID INT, Items VARCHAR(5))
INSERT INTO  @tbl VALUES
(1,'A'),(1,'A'),(1,'A'),(1,'B'),(1,'D'),(2,'A'),(2,'A'),(2,'B'),
(2,'B'),(2,'B'),(3,'A'),(3,'A'),(3,'B'),(3,'B'),(3,'C'),(3,'C'),
(3,'C'),(3,'E'),(3,'F')

T-SQL:

;WITH NotUniqueCTE AS (
    SELECT 
        ID,
        Items,
        CASE 
            WHEN COUNT(*) OVER (PARTITION BY ID, Items) > 1 THEN 0
            ELSE 1
        END AS Item_Unique,
        CASE 
            WHEN 
                (Items <> 'A') AND 
                (COUNT(*) OVER (PARTITION BY ID, Items) = ROW_NUMBER() OVER (PARTITION BY ID, Items ORDER BY Items DESC)) THEN 2 
            ELSE 1 
        END AS RN_NotUnique
    FROM @tbl
), UniqueCTE AS (
    SELECT 
        *,
        DENSE_RANK() OVER (PARTITION BY ID ORDER BY Item_Unique DESC, Items) +
        MAX(CASE WHEN Item_Unique = 0 THEN RN_NotUnique END) OVER (PARTITION BY ID)
        AS RN_Unique
    FROM NotUniqueCTE
)
SELECT 
    ID,
    Items,
    CASE
        WHEN Item_Unique = 1 THEN RN_Unique
        ELSE RN_NotUnique
    END AS RN
FROM UniqueCTE
ORDER BY ID, Items

Output:

ID  Items   RN
1   A       1
1   A       1
1   A       1
1   B       2
1   D       3
2   A       1
2   A       1
2   B       1
2   B       1
2   B       2
3   A       1
3   A       1
3   B       1
3   B       2
3   C       1
3   C       1
3   C       2
3   E       3
3   F       4
user_65780800

Please see below query, the bulk of the ranking is done using the dense_rank function then exceptions like the B value on ID 2 which can be either 1 or 2 and C for id 3 which can be 1 or 2 can be handled using a second ranking.

DECLARE @tbl table (ID INT, Items VARCHAR(5))
INSERT INTO  @tbl VALUES
(1,'A'),(1,'A'),(1,'A'),(1,'B'),(1,'D'),(2,'A'),(2,'A'),(2,'B'),
(2,'B'),(2,'B'),(3,'A'),(3,'A'),(3,'B'),(3,'B'),(3,'C'),(3,'C'),
(3,'C'),(3,'E'),(3,'F')


select case when items='C' and rowno in (1, 2) then 1 when Items='B' and rowno in (2, 3) then 1 when ID=3 and Items='B' and rowno=5 then 1 when ID=3 and Items='C' and rowno=3 then 2
when Items='E' then 3 when Items='F' then 4 else rn end as rn, ID, rowno, Items from 
(
select ROW_NUMBER()over(PARTITION by items order by id) rowno, DENSE_RANK()over(partition by id 
order by items) rn, * from @tbl
) x
order by ID, Items;

oa

user_83358970
Related