Exam 70-761 topic 1 question 41 discussion

More A Than C: DROP TABLE #ERRORS 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,10,'ERROR2'), (4,2,4,'ERROR2'), (5,2,14,'ERROR2'), (6,2,24,'ERROR1'), (7,3,10,'ERROR3'), (8,3,24,'ERROR3') SELECT DISTINCT SERVERID, LOGMESSAGE FROM #ERRORS AS E1 WHERE OCCURENCES > ALL ( SELECT E2.OCCURENCES FROM #ERRORS AS E2 WHERE E2.LOGMESSAGE = E1.LOGMESSAGE AND E2.SERVERID <> E1.SERVERID) SELECT DISTINCT SERVERID, LOGMESSAGE FROM #ERRORS AS E1 WHERE LOGMESSAGE IN ( SELECT TOP 1 E2.LOGMESSAGE FROM #ERRORS AS E2 WHERE E1.LOGMESSAGE = E2.LOGMESSAGE AND E1.SERVERID <> E2.SERVERID ORDER BY OCCURENCES )

C returns all errors for all servers. A is fine except that it will fail to return the entry when more than one server share the same highest occurrence of an error / errors.

Going further on the data set up by @Bartek. The best option should look like this: SELECT DISTINCT SERVERID, LOGMESSAGE FROM #ERRORS AS E1 WHERE OCCURENCES > ALL ( SELECT E2.OCCURENCES FROM #ERRORS AS E2 WHERE E2.LOGMESSAGE = E1.LOGMESSAGE AND E2.SERVERID <> E1.SERVERID) GROUP BY OCCURENCES, SERVERID, LOGMESSAGE HAVING OCCURENCES = MAX(OCCURENCES) This isn't part of the answer tho. So A comes closest

The correct answer is A The subquery is joining to the main query on the LogMessage. So for each LogMessage, return the rows where ALL the rows in the subquery have fewer Occurrences. There is a filter to not compare a server with itself. The query could be modified to leave out the e1.ServerID <> e2.ServerID can be left out. And we would then have the following fragment, which I think is more readable WHERE Occurrences >= ALL (....)

Correct Answer: A

Frankly speaking none of existing solution seems clear/complete to the questions. Therefore come up with something new and try to make it look elegant. --kindly note: since question only asked most occurrences so in case there are same max occurrences for the same error message across different servers, I used serverid as tiebreak. with MOST_OCCURRENCES as ( select SERVERID, LOGMESSAGE, OCCURRENCES, ROW_NUMBER( ) over (PARTITION BY LOGMESSAGE order by OCCURRENCES, SERVERID desc) as occurrences_rank from ERRORS ) select DNS, LOGMESSAGE from MOST_OCCURRENCES mo join SERVERS sv on mo.SERVERID=sv.SERVERID where occurrences_rank=1 order by OCCURRENCES desc;