SQL Server Ranking issue

Question

I am trying to apply ranking to my data set the logic is as follows:

For each ID , Order by ID2 ASC and Order by IsMaster Desc rank the row 1 and only change it when the ID4 value changes

My dataset and desired output looks like:

Test data

CREATE TABLE Test_Table 
(ID INT ,ID2 INT, IsMaster INT, ID4 VARCHAR(10))
GO

INSERT INTO Test_Table  (ID ,ID2 , IsMaster , ID4 )
VALUES 
 (1,    101,    1   ,'AAA')  -- 1  <-- Desired output for rank
,(1,    102,    0   ,'AAA')  -- 1
,(1,    103,    0   ,'AAB')  -- 2
,(1,    104,    0   ,'AAB')  -- 2
,(1,    105,    0   ,'CCC')  -- 3
,(2,    101,    1   ,'AAA')  -- 1
,(2,    102,    0   ,'AAA')  -- 1
,(2,    103,    0   ,'AAA')  -- 1
,(2,    104,    0   ,'AAB')  -- 2
,(2,    105,    0   ,'CCC')  -- 3

this is what I have tried so far:

SELECT *
    ,DENSE_RANK() OVER (PARTITION BY ID  ORDER BY ID2 ASC, IsMaster DESC ) rn
FROM Test_Table

please please please help me thank you.

Juan Carlos Oropeza · Answer 1 · 2017-03-15T15:25:43.747

This is a island/gap problem.

First you use LAG() to see if you have a different ID4 on the same partition.
- Is important you also need partition by IsMaster
Then you create the islands when ID4 changes.
Finally use comulative SUM() to get the proper rank.

Sql Demo

WITH id4_change as (
    SELECT  *, 
            LAG(ID4) OVER (PARTITION BY ID, IsMaster ORDER BY ID2) as prev
    FROM Test_Table
), islands as (
    SELECT *, 
           CASE WHEN ID4 = PREV 
                THEN 0 
                ELSE 1 
           END as island
    FROM id4_change
) 
SELECT *,
       SUM(island) OVER (PARTITION BY ID, IsMaster ORDER BY ID2) rank
FROM islands       
ORDER BY ID, ID2, IsMaster DESC
;

OUTPUT: You can see when ID4 = PREV doesnt create a new "Island" so have same rank.

EDIT: You can simplify first two querys

WITH id4_change as (
    SELECT  *, 
            CASE WHEN ID4 = LAG(ID4) OVER (PARTITION BY ID, IsMaster ORDER BY ID2)
                 THEN 0
                 ELSE 1
            END as island
    FROM Test_Table
) 
SELECT *,
       SUM(island) OVER (PARTITION BY ID, IsMaster ORDER BY ID2) rank
FROM id4_change       
ORDER BY ID, ID2, IsMaster DESC
;

@gordatron Is a window function similar to `ROW_NUMBER()` also have `LEAD()` to get next value. — Juan Carlos Oropeza, Mar 15 '17 at 15:25
Thank you @JuanCarlosOropeza this is very close but if the ID4 changes in the very second row it still shows the same rank. For example if ID 1 has `AAB` right after the very first `AAA` value it gives both first rows the rank of `1` :( . — Jack Jones, Mar 21 '17 at 17:54
Can you update the rextexter demo with the sample data and share the new link? So i can take a look? Right now dont understand the sample. — Juan Carlos Oropeza, Mar 21 '17 at 21:11

score 1 · Accepted Answer · answered Mar 21 '17 at 18:04

Another way probably less efficient but it will work.

WITH X AS
(
 SELECT *
       ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID2) RowNum
 FROM dbo.Test_Table
)
, CTE_VehicleNumber
as
(
    SELECT  T.ID , T.ID2, t.IsMaster ,T.ID4 , t.RowNum  , 1 as [Rank]
    FROM X  as T
    WHERE T.IsMaster = 1 

    UNION ALL

    SELECT  T.ID, T.ID2, t.IsMaster ,T.ID4 , t.RowNum , CASE WHEN t.ID4 <> c.ID4 THEN 1+ C.[Rank]
                                                              ELSE 0+ C.[Rank]
                                                         END  as [Rank]
    FROM CTE_VehicleNumber as C
        inner join X  as T ON T.RowNum = C.RowNum + 1
                                              AND t.ID = c.ID
)
SELECT ID , ID2, IsMaster ,ID4  , [Rank] 
FROM CTE_VehicleNumber 
ORDER BY ID , ID2, IsMaster ,ID4  , [Rank]
OPTION (MAXRECURSION 0);

thank you very much sir, this is exactly what I wanted. thank you very much again. — Jack Jones, Mar 22 '17 at 09:57

score 0 · Answer 3 · answered Mar 15 '17 at 14:17

Are you sure that your orders of ID2 and IsMaster affect the desired result, considering the rest of the data in ID and ID4?

I just tried to use the following code:

; WITH CTE AS (
    SELECT DISTINCT ID, ID4, DENSE_RANK() OVER (ORDER BY ID4) Rnk
    FROM #Test_Table
)
SELECT t.*, c.Rnk
FROM #Test_Table t
    INNER JOIN CTE c ON t.ID = c.ID AND t.ID4 = c.ID4;

... and even with changing the order of ID2 and IsMaster I can't get it to "misbehave" - IF there's only one IsMaster = 1 per a group of ID4's and no duplicates in ID2.

SQL Server Ranking issue

3 Answers3