Imran Ashraf-452633 (11/4/2010)
Good question learned something new today.
(Not targeting you specifically, but everyone who posted and/or will post similar comments)
The question is good indeed - but please don't learn too much from this.
If you take away the knowledge that you can use the OVER() clause for all aggregates except CHECKSUM, 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:
SELECT MAX(TaxRate), StateProvinceID
GROUP BY StateProvinceID
ORDER BY StateProvinceID;
Much easier to understand and maintain in the future, and a more efficient execution plan to boot!
(EDIT: Fixed a silly typo - thanks, Oleg, for the heads-up!)
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis