This has more to do with the game's balance than its story/ending, but after so many years of listening to Dancing Mad and imagining what the fight must be like, I wish Final Fantasy VI's final dungeon was a lot more difficult. For reference, I replayed FFIV over the summer, and that final dungeon still kicks ass, even after doing all the side content (all three quests, lol)! Ah well, that's what fan hacks and challenge runs are for.

Considering that I'm actually terrible at seeing RPGs all the way through, I can't really contribute to this conversation beyond that. Story-wise I've yet to play an RPG with an ending that failed to deliver. I will say as a non-RPG example, getting to the end of Jak 3 and discovering that (SPOILER)the Precursor's were ottsels all along and they had wavering faith in Jak's abilities and were considering a Plan B caused my eleven year old self to turn the game off and lie awake in bed knowing that the revelation to one of the biggest mysteries of my then-favorite game series was done through a series of cheap gags in one tacked-on cutscene.