CODEWITHSUNDEEP
oracle-databasereporting-servicesplsqlrownum
Peter
Peter

Reputation: 14128

Increment column A when column B changes value

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.

Upvotes: 1

Views: 1686

Answers (2)

the_slk
the_slk

Reputation: 2182

select  name
,       value
,       hour
,       dense_rank() over (partition by 1 order by name) as row_num
from
(
        select 'Peter' name,  '1' hour , '10' value from dual union all
        select 'Peter',  '2', '12' from dual union all
        select 'Peter',  '3', '22' from dual union all
        select 'Joe',    '1', '8'  from dual union all
        select 'Joe',    '2', '8'  from dual union all
        select 'Joe',    '3', '12' from dual union all
        select 'Richard','1', '9'  from dual union all
        select 'Richard','2', '7'  from dual union all
        select 'Matt',   '1', '11' from dual
)

Joe     8   2   1
Joe     12  3   1
Joe     8   1   1
Matt    11  1   2
Peter   22  3   3
Peter   12  2   3
Peter   10  1   3
Richard 9   1   4
Richard 7   2   4

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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.

Upvotes: 4

Related Questions