Exam 70-761 topic 1 question 177 discussion

Actual exam question from Microsoft's 70-761

Question #: 177
Topic #: 1

DRAG DROP -
You create a table to track sales persons by running the following Transact-SQL statement:

You need to create a report that shows the sales people within each territory for each year. The report must display sales people in order by highest sales amount.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

Show Suggested Answer

Suggested Answer:

Ranking rows results in the partition of a result set being returned.
PARTITION BY divides the query result into partition.
ORDER BY defines the logical order of the rows.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-2017

by MarcusJB at March 4, 2020, 9:29 a.m.

Comments

Submit Cancel

MarcusJB

Highly Voted 5 years, 2 months ago

From my point of view it should be "... ORDER BY Sales DESC)" if you like to have the highest sales amount on rank 1, but that's not included in the answer segments.

upvoted 8 times

engintayfun

5 years ago

it is already ordering by sales, but first 'partition by' has to come. partition by territory and year and then order by sales

upvoted 1 times

...

BabyBee

Most Recent 4 years, 5 months ago

Sample code: DROP TABLE IF EXISTS #SalesPerson; CREATE TABLE #SalesPerson ( id int NOT NULL ,TerritoryID int NULL ,Sales MONEY NOT NULL ,EntryDate DATETIME NOT NULL ); INSERT INTO #SalesPerson VALUES (1, 1, 5000, '10/12/2015 15:22:00') ,(2, 1, 8000, '1/12/2015 13:00:00') ,(3, 1, 12000, '1/12/2016 13:00:00') , (4, 2, 15000, '03/10/2016 15:22:00') , (5, 2, 18000, '12/10/2016 15:22:00') ,(6, 3, 7000, '4/01/2017 13:00:00') ,(7, 3, 4000, '5/02/2017 13:00:00'); --SELECT * FROM #SalesPerson; SELECT id, territoryID, Sales, YEAR(EntryDate) AS [Year], RANK() OVER (PARTITION BY TerritoryID, YEAR(EntryDate) ORDER BY Sales DESC) AS Ranking FROM #SalesPerson

upvoted 4 times

...

stm22

4 years, 10 months ago

yes i agree order by sales DESC

upvoted 3 times

...

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam