SIMULATION
You need to create a query that meets the following requirements:
The query must return a list of salespeople ranked by amount of sales and organized by postal code. The salesperson who has the highest amount of sales must be ranked first.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within code that has been provided as well as below it.
Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.
A. Please review the explanation part for this answer
Correct Answer: A
Explanation/Reference:
Explanation:
1 SELECT RowNumber() OVER(PARTITION BY PostalCode ORDER BY SalesYTd DESC) AS "Ranking", 2 p.LastName, s.SalesYTD, a.PostalCode3 FROM Sales.SalesPerson AS a etc
On line 1 add: RowNumber
One line 1 add: PARTITION BY
ROW_NUMBER() numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
SYNTAX for OVER:
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
Example: Using the OVER clause with the ROW_NUMBER function
The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber, FirstName, LastName,
FirstName, LastName, CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1 GROUP BY LastName, FirstName; Here is a partial result set.
RowNumber FirstName LastName SalesQuota
——— ——— —————— ————-
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql
Syntax Error
1 SELECT Row_Number() OVER(PARTITION BY ….
not RowNumber
See your own Explanation