Add into your SELECT ROW_NUMBER() function, partition over the date, ORDER BY DESC
The details depend on your database - it could be ROWNUM, ROW_NUMBER or maybe some other function. Do the research, this simple query should be very simple to implement.
This is for SQL Server
SELECT * ,ROW_NUMBER() OVER (PARTITION BY YEAR(your_date_field), MONTH(your_date_field) ORDER BY your_date_field DESC) AS sno
FROM your_table
NOTE that you shouldn't have SELECT * in production as it tends to break as new joins or views are added (ambigous fields) or you depend on the order of the fields for something. State explicitly what you're returning from the query.
This might be useful for mySQL (I didn't work with MySQL before):
MySQL RowNumber example[
^]
If this helps, please take time to accept the solution. Thank you.