DENSE_RANK() – What’s wrong here?

I have a very simple table as follows:

ID  NAME PRICE
1   A   10.45
2   B   8.25
3   A   10.45
4   C   5.00
5   D   4.00
6   E   10.45

When running the DENSE_RANK()

select [name], [price], 
       DENSE_RANK() over (PARTITION BY [name], [price] ORDER BY [name],[price]) as drank
  from temp

I get the below. I was hoping to see that the second row should have drank of 2

name price  drank
A   10.45   1
A   10.45   1  //this should be 2, isn't
B   8.25    1
C   5.00    1
D   4.00    1
E   10.45   1

Answer

The problem here is your misunderstanding of ranking functions. From DENSE_RANK (Transact-SQL)

Remarks

If two or more rows have the same rank value in the same partition, each of those rows will receive the same rank.

The same is true for RANK. This means that, for your data, as both name and price have the same values, they are given the same rank; in this case 1. The difference between the 2 functions is how they handle rows after equal rows. DENSE_RANK will increment sequentially for each “new” rank, where as RANK will skip rankings where there are rows with equality. I.e. 1,1,2,3 and, 1,1,3,4 respectively.

What you clearly want here, however is ROW_NUMBER. I do note, however, that partitioning and ordering by the same columns normally is a flaw as well, as what ever row is numbered first is arbitrary, and that arbitrary numbering might not be the same each time. Ideally you should be ordering by another column that provides an explicit order; perhaps an ID:

SELECT name,
       price,
       ROW_NUMBER() OVER (PARTITION BY name, price ORDER BY SomeOtherColumn) AS RN
FROM dbo.YourTable;

If you don’t have a column to order by, you can use an arbitrary value, or even (SELECT NULL) as Gordon has done in their answer.