-- Ranking of orders by OrderTotal per Account/Year

SELECT

A1.Account

,Year(SO1.OrderDate) OrderYear

,Rank() OVER(Partition by A1.Account,Year(SO1.OrderDate) Order By SO1.OrderTotal Desc) AS Rank_Num

,SO1.OrderTotal OrderTotal

FROM SalesOrder...