I've had a similar situation in the past and resolved by using a CTE and row ranking. I've created a test table similar to the one provided that has the...