I have a Hoover SpinScrub (one of the many versions) and don't like it that much. I mean, it's probably great if you start using it when your carpet is brand new and clean every 4 months or so, but on dingy carpet I don't think it does a good enough job. I should probably have my carpets professionally cleaned and then every 3 months use the Hoover. We're hoping to get new carpet in a year or so though.
The "best" are the huge professional Rug Doctor's I think. I never had good luck with the rental ones though.