Great point, and it is one that I've brought up to the team. They say we'll look at that when we get the budget, but that may be months off.

For the time being they seem to like the idea of using a Max(Row_Number()) approach. There are very few records this actually will impact, so they are willing to take the hit on accuracy. The worst that will happen is someone shows up to a property where access has been revoked, and will discuss it with the owner again. If they don't get new permissions then they'll update the system with the new record, and that one will be taken care of.

Even with that, I keep chasing the logic circle around. I'll be working on it for most of the rest of the afternoon, so if I find the answer then I'll be sure to post it.