See also Markus Winand's blog and http://use-the-index-luke.com/sql/partial-results/window-fun...

and Joe Celko https://www.red-gate.com/simple-talk/sql/t-sql-programming/w...