Increment column A when column B changes value

Question

I'm using PL/SQL (Oracle) and this is the result I have from a simple select query at the moment (with an order by):

Name        Hour        Value
---------------------------------
Peter       1           10
Peter       2           12
Peter       3           22
Joe         1           8
Joe         2           8
Joe         3           12
Richard     1           9
Richard     2           7
Matt        1           11

In SSRS, I have a complex Matrix where I want to have alternating row colors*. Therefor, I need the rownumber, but it must be 'grouped' by the Name. So this is what I want:

Name        Hour        Value        Row_Num
--------------------------------------------
Peter       1           10           1
Peter       2           12           1
Peter       3           22           1
Joe         1           8            2
Joe         2           8            2
Joe         3           12           2
Richard     1           9            3
Richard     2           7            3
Matt        1           11           4

Notice how the Row_Num (or whatever you want to call it) only changes when the Name changes. Is this possible in PL/SQL?

*I know of the techniques to get alternating row colors in SSRS, but I'm using a matrix and have the problem mentioned by Kyle Hale in the comments to ahmad's answer to this question.

Answer 1

This is easy to do with the dense_rank() function in the query used to fetch the results:

select name, hour, value,
       dense_rank() over (order by name) as row_num
from t;

Note: this will not necessarily assign the values in the order you have given. But each group will get a different value. If you need them in the order given, then you will need to identify the order. SQL tables are inherently unordered, so a column is needed to specify the ordering.