Count the instance of a returned value from a SQL query

Question

Is there a way to count the instances of a returned value and display it in a new column. Like this. All I have found is how to count the total number.

apple   1
apple   2
apple   3
pear    1
pear    2
grape   1
grape   2

Answer 1

I believe you are looking for row_number():

select val, row_number() over (partition by val order by val) as seqnum
from t;