Counting the rows having the same 3 fields but one different field
HesipesiFeb 21 2013 — edited Feb 21 2013Hi,
I have a table with 5 rows. customerid,bankid,sixid,favcode,idx
In this table there are many rows which are having the same customerid,bankid and favcode BUT different sixcode
111,222,333,555,0
111,222,444,555,0
111,222,666,555,0
111,222,777,555,0
111,222,888,555,0
-------
111,333,222,555,0
111,333,444,555,0
111,333,777,555,0
111,333,888,555,0
111,333,999,555,0
111,333,666,555,0
I want to update the idx in a way to count these records and reset in every new round.
for instance it would be like this after the update.
111,222,333,555,0
111,222,444,555,1
111,222,666,555,2
111,222,777,555,3
111,222,888,555,4
111,333,222,555,0
111,333,444,555,1
111,333,777,555,2
111,333,888,555,3
111,333,999,555,4
111,333,666,555,5
Do you have any idea about the SQL or PL/SQL which can do this?
Note that the same records are not necessarily after each other in the database so there must be a full table scan to find out the records which have 3 same fields.
If there is any ambiguity in my question please let me know and i'll explain more.
Many thanks for your help.