-- 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 SO1
Inner Join Account A1 on A1.AccountID = SO1.AccountID
WHERE
A1.Account Like 'Milford Ex%' And
SO1.OrderTotal > 0
Order By 1, 2, 3