Why is Rank() not working as it should?

Question

Hello,

This is my table data :-

Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Lux','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Crowning Glory','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (2,'Cinthol','nice soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (3,'Lux','nice soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (3,'Lux','nice soap','soap');

When i issue this query :-

Select  ProductId, 
        ProductName, 
        Description,
        Category, 
        RANK() Over ( partition by ProductId Order By ProductId) As Rank
From tblProduct;

All i can see is 1st rank in each row. Where are all other other ranks? row with productid 2 should have rank 6 since i am not using DENSE_RANK(). Why is the query not working?

Answer 1

You're partitioning by ProductId - which means each 'partition' will start RANKing from 1. Try removing the PARTITION BY ProductId.