How to add a identity column that is unique per grouping

Say I have 2 columns

Product     Product_Cat
-------     -----------
Cat         0
Dog         0
Potatoes    2
Carrots     2
Laundry     开发者_StackOverflow中文版1
Bird        0

I want to add on a 3rd identity column but I want the numbering to be unique per Product_Cat So the output would look like

Product     Product_Cat     Cat_Ident
-------     -----------     ---------
Cat         0               1
Dog         0               2
Potatoes    2               1
Carrots     2               2
Laundry     1               1
Bird        0               3

How do you do this?

This of course is not my real data but a simplification of what I want to do. in my live system I have 4585 different values of "Product_Cat" and they range from 1 to 2408 "Products" in the category.

You need to use RANK() as follows:

CREATE TABLE #Products
(
    ID int IDENTITY(1,1),
    Product nvarchar(8),
    Product_Cat int
)
GO

INSERT INTO #Products (Product, Product_Cat)
VALUES ('Cat', 0)
,('Dog', 0)
,('Potatoes', 2)
,('Carrots', 2)
,('Laundry', 1)
,('Bird', 0)
GO

ALTER TABLE #Products
    ADD Cat_Ident int
GO

UPDATE #Products
    SET Cat_Ident = rankVal
FROM #Products 
    INNER JOIN (
        SELECT ID, RANK () OVER (PARTITION BY Product_Cat ORDER BY ID ) AS rankVal
        FROM #Products ) rankings ON #Products.ID = rankings.ID

SELECT * FROM #Products

DROP TABLE #Products

Result is:

ID          Product  Product_Cat Cat_Ident
----------- -------- ----------- -----------
1           Cat      0           1
2           Dog      0           2
3           Potatoes 2           1
4           Carrots  2           2
5           Laundry  1           1
6           Bird     0           3

(6 row(s) affected)

We need more info but it looks like you will need to use a trigger to form the value of cat_ident.

A simple SELECT COUNT()+1 GROUP BY ProductCat should help

INSERT INTO Products
           (Product
           ,Product_Cat
           ,Cat_Ident)
     VALUES
           ('Flower'
           ,1
           , (select ISNULL( (SELECT top (1) Cat_Ident +1  as x
               FROM Products
              where Product_Cat =1
                    order by  Cat_Ident desc),'1') ))