Exam 70-461 topic 2 question 5 discussion

Actual exam question from Microsoft's 70-461

Question #: 5
Topic #: 2

You administer a Microsoft SQL Server database that includes a table named Products. The Products table has columns named ProductId, ProductName, and
CreatedDateTime.
The table contains a unique constraint on the combination of ProductName and CreatedDateTime.
You need to modify the Products table to meet the following requirements:
✑ Remove all duplicates of the Products table based on the ProductName column.
✑ Retain only the newest Products row.
Which Transact-SQL query should you use?

A. WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON cte.ProductName = p.ProductName AND cte.CreatedDateTime > p.CreatedDateTime
B. WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName AND p.CreatedDateTime > cte.CreatedDateTime
C. WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName ) DELETE p FROM Products p JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName
D. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE Products FROM Products p

Show Suggested Answer

Suggested Answer: B 🗳️

by M62 at Sept. 4, 2019, 10:51 a.m.

Comments

Submit Cancel

M62

Highly Voted 5 years, 10 months ago

Answer B is wrong; the MIN aggregate function finds the oldest date and then the record in the table that new than this date are deleted. Not what was asked for.

upvoted 5 times

...

Sugar

Most Recent 5 years, 2 months ago

i think B is the answer but there was a typo error. instead of MIN should be MAX. rest all are correct

upvoted 1 times

...