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
)