Dense_rank in sql

Question

I have the following table 'CarN' in sql server

    carID      ownerID      Make      Model         Year        Color
    C11         O11         Honda       A           2010        Red       
    C12         blue        Honda       B           2012        Blue      
    C13         O12         Maru        B           2014        Yellow    
    C12         blue        Honda       B           2012        Blue 

when I execute the query

select  *,dense_Rank() over(partition by model order by carid)
from carN

carID      ownerID      Make      Model         Year        Color     Rank
C11         O11         Honda       A           2010        Red         1
C12         blue        Honda       B           2012        Blue        1
C12         blue        Honda       B           2012        Blue        1
C13         O12         Maru        B           2014        Yellow      2

In result how it happened to get the same number '1' for the first three records ?

Answer 1

Let's partition your data set according to your SQL statement:

dense_Rank() over(partition by model order by carid)

Model        carID      Rank
  A          C11          1
  -------------------------
  B          C12          1
  B          C12          1
  B          C13          2

The first partition (Model A) only has 1 row so Rank = 1

The first two rows of the second partition have the same carID, so both of them have Rank = 1. If you want them to have different rank, add in a tie-breaker or use ROW_NUMBER:

-- this will still give the same rank when the tie-breaker column is equal
dense_Rank() over(partition by model order by carid, AnotherColumn)


-- guaranteed to give different ranking for each row with a partition
-- if the rows have the same carID, which row gets what number is undetermined
ROW_NUMBER() over(partition by model order by carid) 

Answer 2

The way dense rank works is like below:

1. First partition by the partition by clause; which is model. So here are two partitions as there are two models

partition 1

carID      ownerID      Make      Model         Year        Color
C11         O11         Honda       A           2010        Red

partition 2

carID      ownerID      Make      Model         Year        Color    
C12         blue        Honda       B           2012        Blue      
C13         O12         Maru        B           2014        Yellow    
C12         blue        Honda       B           2012        Blue 

2. In each partition give ranks ordering by the order by clause which is carID. Note that in partition 2 since two records have same carID they will be given same rank, and since car12 is less than in value than car13 and default order here is ASC, so both records with car12 will have same rank as 1

partition 1

carID      ownerID      Make      Model         Year        Color  rank
C11         O11         Honda       A           2010        Red     1 as it is the only record

partition 2

carID      ownerID      Make      Model         Year        Color    rank
C12         blue        Honda       B           2012        Blue      1
C13         O12         Maru        B           2014        Yellow    2
C12         blue        Honda       B           2012        Blue      1

3. Now collate all partitions together to get the output that you see.