SQL Server Rank Function to get Top N Sales from a Table

How to use Rank () Function to get Top N and Bottom N Sales from a Table in SQL Server
Suppose we have the Sales data as below. 

From this data, we wants to extracts the Top N ( Eg : Top 3) Sales from each Region.
We can do this by using the Rank() or Row_Number() function with CTE as follows..

With MyCte1
As
(
 Select *,
 Rank () Over ( Partition by [SalesRegion_Id] Order by [Net_Sales] Desc) Rank_N
 From [dbo].[FactSales]
 )
Select Top 9 * From  MyCte1 Where Rank_N IN(1,2,3)
;

Output :

With MyCte1
As
(
 Select *,
 Row_Number () Over ( Partition by [SalesRegion_Id] Order by [Net_Sales] Desc)  Rank_N From [dbo].[FactSales]
 )
Select Top 9 * From  MyCte1 Where Rank_N IN(1,2,3)
;

Output :

In the similar manner, We can find the Top N and Low N sales using the Multiple CTE as follows..

With MyCte1 As
  (
  Select *,
  Rank  () Over ( Partition by [SalesRegion_Id] Order by [Net_Sales] Desc) Rank_Max From [dbo].[FactSales]
  ),
MyCte2 AS
  (
  Select *,   Rank () Over ( Partition by [SalesRegion_Id] Order by [Net_Sales] Asc)

Rank_Min From [dbo].[FactSales]
  )

Select Top 3 MyCte1.[SalesOrder_Id] [Top_SO_Id],MyCte2.[SalesOrder_Id] [Low_SO_Id],
MyCte1.[SalesRegion_Id],MyCte1.[Net_Sales] AS [Top_Sales],
MyCte2.[Net_Sales] AS [Low_Sales] 
From MyCte1 Left Join
MyCte2 ON  MyCte1.[SalesRegion_Id]=MyCte2.[SalesRegion_Id]
Where (Rank_Max=1 AND Rank_Min=1)
;

Output :

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------