RANK() with partition by is not reset

Transact-SQL
1

I have the following data set.
when I try to simpley rank by partitioning UID & T as follows in order to rank the id's consecutively:

RANK() OVER (PARTITION BY [UID], T ORDER BY Id) AS RN

I get the following data. Please note, the ranking works well on the first few partitions, but is NOT reset further on:

Pasted image697×823 18.3 KB

Any suggestions?

2

RANK function is working very well.

Check this sample:

DECLARE  @Table1 AS TABLE
    ([ID] int, [UID] int, [T] varchar(16))
;
    
INSERT INTO @Table1
    ([ID], [UID], [T])
VALUES
    (8954379, 328814, 'Out of the money'),
    (8954823, 328814, 'Out of the money'),
    (8955533, 328814, 'In the money'),
    (8955625, 328814, 'In the money'),
    (8959129, 328814, 'Out of the money'),
    (8976073, 328814, 'Out of the money'),
    (8976535, 328814, 'Out of the money'),
    (8977415, 328814, 'Out of the money'),
    (8979701, 328814, 'In the money'),
    (8985957, 328814, 'Out of the money')
;


SELECT 
    ID, UID, T
    ,RANK() OVER (PARTITION BY [UID], T ORDER BY Id) AS RN
FROM
    @Table1
ORDER BY
    ID
        

SELECT 
    ID, UID, T
    ,RANK() OVER (PARTITION BY [UID], T ORDER BY Id) AS RN
FROM
    @Table1
ORDER BY
    UID,T

If I understand correctly, the Rank/Row_Number should be reset after every change in UID,T but keeping the order ID.

SELECT
    T1.ID,
    T1.UID,
    T1.T
    --,T2.minID
    --,T2.maxID
    ,ROW_NUMBER() OVER(PARTITION BY T1.UID, T1.T ,T2.minID ORDER BY T1.Id) AS RN
FROM
    @Table1 AS T1
    INNER JOIN
    (
        SELECT 
            UID,T,
            MIN(ID) AS minID,
            MAX(ID) AS maxID    
        FROM
        (
            SELECT 
                ID, UID, T    
                ,ROW_NUMBER() OVER (ORDER BY Id) - ROW_NUMBER() OVER (PARTITION BY [UID], T ORDER BY Id) AS RK
                ,ROW_NUMBER() OVER (ORDER BY Id) AS RK_all
                , ROW_NUMBER() OVER (PARTITION BY [UID], T ORDER BY Id) AS Rk_2
            FROM
                @Table1
        )A
        GROUP BY
            UID,T
            ,RK
    ) AS T2
    ON T1.ID>=T2.minID
    AND T1.ID<=T2.maxID
ORDER BY 
    T1.ID

The output for this:

ID          UID         T                RN
8954379     328814      Out of the money 1
8954823     328814      Out of the money 2
8955533     328814      In the money     1
8955625     328814      In the money     2
8959129     328814      Out of the money 1
8976073     328814      Out of the money 2
8976535     328814      Out of the money 3
8977415     328814      Out of the money 4
8979701     328814      In the money     1
8985957     328814      Out of the money 1
1 Like
3

stepson Hi,

Thank you so much for you kind reply.
It works well, but there's a fix I had to add into your query:

SELECT [ID], [UID], [T],
ROW_NUMBER() OVER (PARTITION BY [UID] ORDER BY ID) - ROW_NUMBER() OVER (PARTITION BY [UID], T ORDER BY ID) AS RK

That fixed some issues when working with different UID's.

Thanks!

4

Great!
Glad that is working