How can I incrementally count the values of an sql result set column in the same result set?

Question

I have a query in OBIEE which delivers the following Result Set.

Is it possible to someway get the last column to display 1 to N for each Phone Number that is the same. This means that instead of 2,2,2,2,1,1,6,6,6,6,6,6 we would in the last column have 1,2,1,2,1,1,1,2,3,4,5,6

I am hitting a mental block here. The SQL used to generate the query mentioned is:

  SELECT "service request"."SR #"                          saw_0,
       "- contact communication"."Mobile Phone #"        saw_1,
       Count("- contact communication"."Mobile Phone #") saw_2
FROM   "service requests"
WHERE  ( "service request dealer"."Local Dealer Number" = '20536' )
       AND ( "- contact communication"."Mobile Phone #" IS NOT NULL )
GROUP  BY saw_1
ORDER  BY saw_1  

I don't mind either modifying the above query or combining or joining it with another query. Please help.

Answer 1

Try this:

SELECT "service request"."SR #"                          saw_0,
       "- contact communication"."Mobile Phone #"        saw_1,
       row_number() over(partition by "- contact communication"."Mobile Phone #"
                         order by "service request"."SR #") saw_2
FROM   "service requests"
WHERE  ( "service request dealer"."Local Dealer Number" = '20536' )
       AND ( "- contact communication"."Mobile Phone #" IS NOT NULL )
ORDER  BY saw_1  

Answer 2

Try this :

SELECT *,
       ROW_NUMBER()
         OVER (
           partition BY mobilephone
           ORDER BY sr )
FROM   yourtable 

Answer 3

Try adding a column with the column formula:

rcount("- contact communication"."Mobile Phone #" by "service request"."SR #")

That should give you a running count of the Mobile Phone #, grouped by the SR #.