How to apply dense rank/ partition to animal table? SQL Server

Question

Table:

animal height width footlength brand  price age 
------------------------------------------------
cow     3   5   2              fuller   231   8
cow     3   5   2              fuller   242   9 
cow     3   5   2              fuller  1000   2 
chicken 2   2   2              tyson     11   1 
chicken 2   2   2              tyson     11   2 
chicken 2   2   2              tyson     11   3
cow     4    5   2             tyson     90 900
cow     4    5   2             tyson     90 900

Okay so in this table I want the groups ranked 1, 2, 3.

Because if animal height width footlength and brand is the same give the rows the same ranking.

The way I have coded it in SQL I keep getting the same rank for each row. vs 3 different ranks.

Any tips on how to better understand dense rank and this problem?

Answer 1

You may use DENSE_RANK as follows:

SELECT *, DENSE_RANK() OVER (ORDER BY height, width, footlength, brand) rnk
FROM yourTable
ORDER BY rnk, animal, height, width, footlength, brand;

Demo

Note that we don't need/want a PARTITION BY clause with DENSE_RANK here, because the rank is to be applied to the entire table. Using a partition would mean that potentially the same rank values would appear more than once.