title: SQL Windowing Ranks Functions
body: <pre><code> SELECT
          *
      FROM (
          SELECT 
  
              Product,
              SalesAmount,
              ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) as RowNum,
              RANK() OVER (ORDER BY SalesAmount DESC) as RankOf2007,
              DENSE_RANK() OVER (ORDER BY SalesAmount DESC) as DRankOf2007
          FROM (
              SELECT
  
                  c.EnglishProductName as Product,
                  SUM(a.SalesAmount) as SalesAmount,
                  b.CalendarYear as CalenderYear
              FROM FactInternetSales a
              INNER JOIN DimDate b
                  ON  a.OrderDateKey=b.DateKey
              INNER JOIN DimProduct c
                  ON  a.ProductKey=c.ProductKey
  
              WHERE   b.CalendarYear IN (2007)
              GROUP BY c.EnglishProductName,b.CalendarYear
              ) Sales
          ) Rankings
      WHERE [RankOf2007] &lt;= 5
      ORDER BY [SalesAmount] DESC
  </code></pre>
  
  <p>I am currently sorting products based on summation of Sales Amount in descending fashion and  getting rank based on the summation of sales amount of every product in 2007 and ranking product 1 if it has the highest Sales Amount in that year and so forth.</p>
  
  <p><img src="https://i.stack.imgur.com/6qMEe.png" alt="My Database data&#39;s image"></p>
  
  <p>Currently my database table looks like the one mentioned in the image (apart from RankOf2008 and DRankOf2008 columns), I would like to have rankings in year 2008 for same top 5 products of 2007 (Null value if any of those top 5 products of 2007 are unsold in 2008) in the same table with side by side columns as shown in the image above.</p>