Adding an ID column based on two other columns
You just need

SELECT Location, 
       Reference, 
       Year,  
       Int1, 
       Int2,
       ROW_NUMBER() OVER (PARTITION BY Year, Reference 
                              ORDER BY Int2 DESC) AS [Id]
FROM Value


To give your desired results (online demo).

In the event of tied Year,Reference,Int2 this will arbitrarily assign a sequential numbering between the tied rows.