Final Fantasy VII
This track rates very highly on this list because it is awesome in every conceivable way. It's grand, it's deep, it's endearing, it's catchy, all of that.
What it isn't is appropriate for the character it's supposed to be the theme for. At all. I don't know about you, but if you describe to me a character who, yes, may be a Jerk With a Heart of Gold deep inside, is nonetheless an over-aggressive loudmouthed, chain-smoking misogynist... this is certainly not the kind of music I would expect to have attached to him. (Actually, I would expect such a character to be a villain, at least to start off. FF7 blurs the lines a little bit.)
Don't get me wrong: Cid is awesome. And his theme music is very awesome. I just don't necessarily see the connection between the two.