Hugo Kornelis (11/4/2010)
(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:
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.
Select StateProvinceID,
TaxType,
Max(TaxRate) Over (Partition by StateProvinceID),
Max(TaxRate) Over (Partition by StateProvinceID, TaxType)
from Sales.SalesTaxRate
Order by StateProvinceID