SQL & PL/SQL

Hi,

I am using Oracle 10.2 version. I have following query which is giving duplicate sak_recip (ID) and Effective_date but different id_medicare (Member). Please ignore column "Row", it is just to represent the row.


select sak_recip, dte_effective, id_medicare from t_re_hib
where sak_recip in
(select sak_recip from t_re_hib
group by sak_recip, dte_effective
having count(1) > 1)
order by sak_recip, dte_effective

Row ID Effective_Date Member
1 13236 20081108 398523640B2
2 13236 20100201 398523640B
3 13236 20100201 478587427M
4 1122975 20081101 355347279M
5 1122975 20081101 343266091D
6 1882725 20081108 395386441B
7 1882725 20120608 395386441D
8 1882725 20120608 395386441D6

I need output in below format. Here "Dummy" column is showing number associated with each row of group and reset to 1 when a new group (combination of ID and Effective_Date) starts. Basically whenever SQL query starts new group it should start numbering to each row of that group. e.g, If you see row number 2 and 3 they have same ID and Effective_Date and "Dummy" column is numbering each row 1 and 2 respectively.

Row ID Effective_Date Member Dummy
1 13236 20081108 398523640B2 1
2 13236 20100201 398523640B 1
3 13236 20100201 478587427M 2
4 1122975 20081101 355347279M 1
5 1122975 20081101 343266091D 2
6 1882725 20081108 395386441B 1
7 1882725 20120608 395386441D 1
8 1882725 20120608 395386441D6 2


Thanks!