AdventureWorks Database: Calculate row numbers for rows between 50 to 60 inclusive

SQL Query - AdventureWorks: Exercise-122 with Solution

122. From the following table write a query in SQL to calculate row numbers for all rows between 50 to 60 inclusive. Sort the result set on orderdate.

Sample table: Sales.SalesOrderHeader

Click to view Full table

Sample Solution:

WITH OrderedOrders AS  
(  
    SELECT SalesOrderID, OrderDate,  
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber  
    FROM Sales.SalesOrderHeader   
)   
SELECT SalesOrderID, OrderDate, RowNumber    
FROM OrderedOrders   
WHERE RowNumber BETWEEN 50 AND 60;

Sample Output:

salesorderid|orderdate              |rownumber|
------------+-----------------------+---------+
       43708|2011-06-02 00:00:00.000|       50|
       43709|2011-06-02 00:00:00.000|       51|
       43710|2011-06-02 00:00:00.000|       52|
       43711|2011-06-03 00:00:00.000|       53|
       43712|2011-06-03 00:00:00.000|       54|
       43713|2011-06-04 00:00:00.000|       55|
       43714|2011-06-04 00:00:00.000|       56|
       43715|2011-06-04 00:00:00.000|       57|
       43716|2011-06-04 00:00:00.000|       58|
       43717|2011-06-04 00:00:00.000|       59|
       43718|2011-06-05 00:00:00.000|       60|

Contribute your code and comments through Disqus.

Previous: Calculate a row number based on SalesYTD ranking.
Next: Return first and last name, and other columns using partition by clause.