SELECT DISTINCT will only drop rows where all column values in the select statement are the same as another row. None of your sample rows matches any other, so none get dropped.
If you use GROUP BY you should be able to get 1 row - but exactly how you do it depends on what you want
SELECT [Rule], MAX([Date]) as [Date], MAX(No) as No
GROUP BY [Rule]
will give you one row per rule, but may not be exactly what you want.