Very good question - I learned something today.
Hugo
If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKPOINT, that's fine. But if you intend to use this query for similar purposes, think again. There are far better ways to get the maximum tax rate per state/province id:
To look at the question and only talk about it's limitations makes one wonder why is it there.
brdudley
One other important take away, although not illustrated in this question, is that a Select can have multiple Over clauses with different Partitions. This is something that Group By cannot do.
Excellent addition to the questions functionality. I learned something more, and have already started playing with tests to see about changes, especially in some complex reports.
Thanks
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan