Execute the following SQL Server T-SQL script in Management Studio Query Editor to rank sales of AdventureWorks Cycles.

-- SQL rank function - SQL row_number over order by

-- SQL common table expression - CTE - SQL over partition by

USE AdventureWorks;

WITH cteSOD

     AS (SELECT SalesOrderID,

                ProductName = Name,

                RANK()

                  OVER(PARTITION BY SalesOrderID ORDER BY p.ProductID) AS Rank

         FROM   Sales.SalesOrderDetail sod

                JOIN Production.Product p

                  ON sod.ProductID = p.ProductID),

     cteSR

     AS (SELECT sohsr.SalesOrderID,

                sr.[Name],

                RANK()

                  OVER(PARTITION BY SalesOrderID ORDER BY sr.[Name]) AS Rank

         FROM   Sales.SalesOrderHeaderSalesReason AS sohsr

                INNER JOIN Sales.SalesReason AS sr

                  ON sr.SalesReasonID = sohsr.SalesReasonID)

SELECT   SeqID = ROW_NUMBER()

                   OVER(ORDER BY soh.SalesOrderNumber),

         soh.SalesOrderNumber,

         ProductName,

         cteSR.[Name] AS Reason

FROM     cteSOD

         LEFT JOIN cteSR

           ON cteSR.SalesOrderID = cteSOD.SalesOrderID

              AND cteSR.Rank = cteSOD.Rank

         INNER JOIN Sales.SalesOrderHeader AS soh

           ON soh.SalesOrderID = COALESCE(cteSOD.SalesOrderID,cteSR.SalesOrderID)

WHERE    cteSR.[Name] is not null

ORDER BY soh.SalesOrderID,

         COALESCE(cteSR.Rank,cteSOD.Rank);

GO

/* Partial results

SeqID SalesOrderNumber  ProductName             Reason

5956  SO54366           Mountain-200 Black, 46  On Promotion

5957  SO54367           Mountain-200 Black, 42  On Promotion

5959  SO54369           AWC Logo Cap            On Promotion

5958  SO54369           Touring-2000 Blue, 50   Price

5960  SO54370           Patch Kit/8 Patches     Price

5961  SO54371           Road-550-W Yellow, 42   Price

5962  SO54373           Patch Kit/8 Patches     Price

*/