How do I get a unique integer for each unique item using ROW_NUMBER() and RANK() within the same que-CodePudding

I know how I would do this using a temp table, but I want to know how to do this using both ROW_NUMBER() and RANK() for my own learning.

Data:

Item    Desc       Qty  Row
ItemA   ItemDescA  10   1
ItemA   ItemDescA  20   2
ItemB   ItemDescB  30   3
ItemB   ItemDescB  40   4
ItemB   ItemDescB  50   5
ItemC   ItemDescC  60   6

Desired Result:

Item    Desc       Qty  Row  ItemRow
ItemA   ItemDescA  10   1    1
ItemA   ItemDescA  20   2    1
ItemB   ItemDescB  30   3    2
ItemB   ItemDescB  40   4    2
ItemB   ItemDescB  50   5    2
ItemC   ItemDescC  60   6    3

My code:

select

    so.*
    , row_number() over(order by so.[Item], so.Qty) row --this gives me the Row column shown above
    --I want to add a single line here using ROW_NUMBER() or RANK() to accomplish this

from

    #StockOrdersData so

CodePudding user response：

The window function Dense_Rank will give you a unique value per value of Item

For example:

with t as (
  select * from(values('ItemA'), ('ItemA'), ('ItemB'), ('ItemB'), ('ItemB'), ('ItemC'))t(Item)
)
select *, Dense_Rank() over(order by item) Itemrow
from t;

CodePudding user response：

dense_rank() will create a rank based on the Order by clause and partition by clause. In your case you are not interested in specific granularity so we don't need the partition by clause.

the rank will be created based on the item name in ASC order. If the names are similar the same rank will be provided. In the case of row_number, if the same names are there, this would have created rank 1,2 for item A and rank 1,2,3 for item B, and 1 for item C. This is because row_number() doesn't look at ties.

Rank() on the other hand would have produced 1,1 for item A and 3,3,3 for item B as it skips the ranks when there is a repetition of values

Your desired results can be achieved by dense rank

select 
*,
dense_rank() over(order by item) as rank
from table