Aeris's death shocked me because it was the first time a major party member died in games for me and I never saw it coming. I was like "woah, Sephiroth is soo gonna get it now. Cloud will save her for sure. Then I thought the rest of the game would be about getting her out of the life stream.
However, like Alan, I was moved a lot more by the relashionship between Tifa and Cloud, especially during the scene when Cloud finds himself. The music in this scene is also quite fantastic.
As for bad endings...
Morrowind's "ending" sucked.
DOOM: I remember wanting to see this so bad but getting a huge disapointment. A skewered bunny. WTF?
X-COM<s ending was also quite crappy with one of the easiest final bosses I've ever faced.
As for best ones, I'd have to say FF7 tops my list. A 30 minute CGI movie depicting the end of the world and its rebirth. I mean... wow. With a little extra after the credits.
Then I'd say Metal Gear Solid, followed by God of War.