1USE AdventureWorks2012;
2GO
3SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD,
4ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC)
5 AS Row
6FROM Sales.vSalesPerson
7WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0
8ORDER BY TerritoryName;
9
1-- Uses AdventureWorks
2
3SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC)
4 AS RowNumber,
5 FirstName, LastName,
6 CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota
7FROM dbo.DimEmployee AS e
8INNER JOIN dbo.FactSalesQuota AS sq
9 ON e.EmployeeKey = sq.EmployeeKey
10WHERE e.SalesPersonFlag = 1
11GROUP BY LastName, FirstName;
12