All I want in a villain is for them to be a badass.
Basically, I want them to be a credible threat, and I want them to have some trait that makes them cool or interesting. I'm willing to let my "badass" requirement slide if the villain is sufficiently funny-like Team Rocket from Pokemon-but I'd rather have a villain who is both scary and funny, like The Joker.
A few examples: Q from Star Trek is unbelievably powerful, has a twisted sense of humor, and makes schemes for his own amusement; these traits combine to make him both threatening and cool. There's another character, from a different show, played by the same actor who portrayed Q: Discord, the chaos god. Discord has the same traits that make me like Q. The other major villain from the show Discord was in was also a badass; Nightmare Moon, who had enough power to be a serious threat, and was cool because she had a booming voice, an imperious demeanor, and the power to shapeshift to fool her enemies. There's also Sovereign from Mass Effect: he too was incredibly powerful, and would certainly have achieved his goal if it weren't for the hero. He was cool because his plan was to wipe out all advanced sentient life in the galaxy, and because he was a sentient spaceship.
Magicmad5511: You might like Littlefinger from "A Song of Ice and Fire."
DFskelleton: Weren't the other members of FOXHOUND just doing it because they were just insane? From what you said, I'd think that you would consider all of them excellent villains.