SQL Windowing Ranks Functions

 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] <= 5
    ORDER BY [SalesAmount] DESC

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.

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.

Answer

May be you require something like this.

First getting ranks for all products then partition by year, that is rank of products year wise and fetching required data with help of CTE.

WITH cte
AS (
    SELECT *
    FROM (
        SELECT Product
            ,SalesAmount
            ,CalenderYear
            ,ROW_NUMBER() OVER (
                PARTITION BY CalenderYear ORDER BY SalesAmount DESC
                ) AS RowNum
            ,RANK() OVER (
                PARTITION BY CalenderYear ORDER BY SalesAmount DESC
                ) AS RankOf2007
            ,DENSE_RANK() OVER (
                PARTITION BY CalenderYear 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] <= 5
        --ORDER BY [SalesAmount] DESC
    )
SELECT a.*
    ,b.DRankOf2007 AS [DRankOf2008]
    ,b.RankOf2007 AS [RankOf2008]
FROM cte a
LEFT JOIN cte b ON a.Product = b.Product
    AND b.CalenderYear = 2008
WHERE a.CalenderYear = 2007
    AND a.[RankOf2007] <= 5