Sorry, also when you include TOP clause, you can remove count(*) from the select list of sub query.

SELECT *

FROM t_retail

where t_Retail.Pcde6P IN

(SELECT top 100 PERCENT

t_Retail.Pcde6P

FROM t_Retail

WHERE

LEN (LTRIM(t_Retail.Pcde6P)) = 6

GROUP BY

t_Retail.Pcde6P

HAVING

Count(t_Retail.Pcde6P) > 2

ORDER BY

Count(*) DESC

)