Exam 70-761 topic 1 question 42 discussion

Actual exam question from Microsoft's 70-761

Question #: 42
Topic #: 1

DRAG DROP -
You have a database that stored information about servers and application errors. The database contains the following tables.

Servers -

Errors -

You are building a webpage that shows the three most common errors for each server.
You need to return the data for the webpage.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location. 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:

by Bartek at Sept. 7, 2019, 4:50 p.m.

Comments

Submit Cancel

Bartek

Highly Voted 5 years, 9 months ago

ORDER BY OCCURENCES DESC

upvoted 14 times

...

kiri2020

Most Recent 4 years, 8 months ago

missing distinct and desc CREATE TABLE #ERRORS ( ERRORID INT PRIMARY KEY, SERVERID INT, OCCURENCES INT, LOGMESSAGE NVARCHAR(MAX) ) INSERT INTO #ERRORS (ERRORID, SERVERID, OCCURENCES, LOGMESSAGE) VALUES (1, 1,3,'ERROR1'), (2,1,2,'ERROR2'), (3,1,25,'ERROR4'), (4,1,24,'ERROR3'), (5,2,4,'ERROR2'), (6,2,14,'ERROR6'), (7,2,24,'ERROR1'), (8,2,24,'ERROR3'), (9,2,25,'ERROR5'), (10,3,10,'ERROR3'), (11,3,24,'ERROR7') select distinct svr.SERVERID , errs.LOGMESSAGE from #ERRORS as svr cross apply ( select top (3) LOGMESSAGE from #ERRORS where SERVERID = svr.SERVERID order by OCCURENCES desc) as errs

upvoted 3 times

...

Robintang0924

5 years, 5 months ago

Besides Bartek mentioned 'desc' issue, this solution seems doesn't work since ServerID exist in both tables and first reference without table name is ambiguous. where ServerID=svr.ServerID

upvoted 2 times

...