Giving a specific number to fields in my Db

I want to give specific unique numbers to the following field in my db. The column looks like:

August 2020
August 2020
August 2020
August 2020
September 2020
September 2020
September 2020
October 2020
November 2020
December 2020
December 2020
January 2021
January 2021
February 2021
March 2021.

I want the output in the following format:

August 2020         1
August 2020         1
August 2020         1
August 2020         1
September 2020      2
September 2020      2
September 2020      2
October 2020        3
November 2020       4
December 2020       5
December 2020       5
January 2021        6
January 2021        6
February 2021       7
March 2021          8

and so on. There can be n number of rows for each of the months name. The result should have unique and incremental identifier on a month basis. And on change of year, it should continue and not restart from 0.

Answer

You can use dense_rank()

select col1, dense_rank() over(order by to_date(col1,'Month YYYY'))
from tablename