﻿ AdventureWorks: Calculate a row number based on SalesYTD ranking

# 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|
```