w3resource

AdventureWorks Database: Calculate a row number based on SalesYTD ranking

SQL Query - AdventureWorks: Exercise-121 with Solution

121. From the following table write a query in SQL to calculate a row number for the salespeople based on their year-to-date sales ranking. Return row number, first name, last name, and year-to-date sales.

Sample table: Sales.vSalesPerson


Click to view Full table

Sample Solution:

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,   
    FirstName, LastName, ROUND(SalesYTD,2) AS "Sales YTD"   
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

Sample Output:

row|firstname|lastname         |Sales YTD |
---+---------+-----------------+----------+
  1|Linda    |Mitchell         |4251368.55|
  2|Jae      |Pak              |4116871.23|
  3|Michael  |Blythe           |3763178.18|
  4|Jillian  |Carson           |3189418.37|
  5|Ranjit   |Varkey Chudukatil|3121616.32|
  6|José     |Saraiva          |2604540.72|
  7|Shu      |Ito              |2458535.62|
  8|Tsvi     |Reiter           |2315185.61|
  9|Rachel   |Valdez           |1827066.71|
 10|Tete     |Mensa-Annan      |1576562.20|
 11|David    |Campbell         |1573012.94|
 12|Garrett  |Vargas           |1453719.47|
 13|Lynn     |Tsoflias         |1421810.92|
 14|Pamela   |Ansman-Wolfe     |1352577.13|

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Find the salary of top ten employees.
Next: Calculate row numbers for rows between 50 to 60 inclusive.


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.