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.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

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