SQL, order of value instance in table

I hope this is a simple issue but I'm not really sure how to even approach it. Let's say I have a table. I do a select statement on that table for several columns. I want to add one more formula column that will show me the instance number/count of a value in a specified column to appear. Basically how many times has that value appeared in the data already. An example will make it much easier.

Let's say I have 2 columns like this:

col1    |    col2

Orange    |    mike

Orange    |    tim

Banana    |    john

Banana    |    mike

Orange    |    jimmy

What I need to get (based on col1) is this:

col1    |    col2    |    newcol

Orange    |    mike    |    1

Orange    |    tim     |   2

Banana    |    john    |    1

Banana    |    mike    |    2

Orange    |    jimmy    |3

I am running under dashDB. However the query I'm running has 7 JOIN statements, a whole lot of subqueries and it is already "group by"'e a whole lot of other columns.

Solution

From my checks DashDB supports analytic functions so this should work.

select col1,col2,
       row_number() over(partition by col1 order by col1,col2 asc ) as newcol
from mytable
order by col1,col2;

Hope this will help Nick