w3resource

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.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

What is the most efficient/elegant way to parse a flat table into a tree?

WITH RECURSIVE MyTree AS (
    SELECT * FROM MyTable WHERE ParentId IS NULL
    UNION ALL
    SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id
)
SELECT * FROM MyTree;

Ref: https://bit.ly/3FPYFFF

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook