select
backup_start_date as [Date],
((f.backup_size/1024)/1024) as [Database Size],
((f.file_size/1024)/1024) as [File Size],
RANK() OVER (PARTITION BY backup_start_date ORDER BY backup_start_date) AS Rank,
rn = ROW_NUMBER() OVER (PARTITION BY MONTH(backup_start_date) ORDER BY backup_start_date DESC)
from msdb..backupfile f
INNER JOIN msdb..backupset s
ON f.backup_set_id = s.backup_set_id
where s.database_name = 'mydatabasename'
and file_type = 'D'
and f.backup_size > 0
order by backup_start_date
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden