row_number() Group by?

Question

I have a data set that contains the columns Date, Cat, and QTY. What I want to do is add a unique column that only counts unique Cat values when it does the row count. This is what I want my result set to look like:

By using the SQL query below, I'm able to get row using the row_number() function.

However, I can't get that unique column that I have depicted above. When I add group by to the OVER clause, it does not work. Does anybody have any ideas as how I could get this unique count column to work?

SELECT     Date,        ROW_NUMBER() OVER (PARTITION BY Date ORDER By Date, Cat) as ROW,    Cat,    Qty FROM SOURCE 

Answer 1

Here is a solution.

You need not worry about the ordering of Cat. Using following SQL you will be able to get unique values for your Date & Cat combination.

SELECT     Date,        ROW_NUMBER() OVER (PARTITION BY Date, Cat ORDER By Date, Cat) as ROW,    Cat,    Qty FROM SOURCE 

Answer 2

DENSE_RANK() OVER (PARTITION BY date ORDER BY cat)