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.