Record counter with reset 1

[nagornyi]

I have two fields in a table, with multiple values of field2 for each field1 value. Like this:

field1  field2
======  ======
  A       232
  B       116
  A       456
  A       565
  B       968

I need a SQL that would include a column counting field2 entries for each value of field1, and resetting for each field1 value. So the result would be:

field1  field2  counter
======  ======  =======
  A       232       1
  A       456       2
  A       565       3
  B       116       1
  B       968       2

 

[LKBrwnDBA]

Use the DENSE_RANK() analytic function.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[nagornyi]

That works. Thanks!