按相同名称和最大数量获取DISTINCT行

I Tried to think more for how to do this and searched but no solutions match what is need . Here is the table that record a Drawing name it is dynamically depending on Drawing Name , Each Drawing inserted as Submitted in Location column and Updated to rejected and inserted with same data as Submitted with New Revision as you see in this image I Tried to think more for how to do this and se