I personally liked the ending to FF7, though I agree that it felt too short to be truly satisfying. It was somewhat gratifying when I learned later that there was a lot more of the story left to be told (and, in fact, that's still true).
Prince of Persia: The Two Thrones - The ending to this was a perfect way to end the trilogy. The Prince has learned that he can't manipulate time to undo his mistakes, and faced his own worst inner nature, and finally found peace. Together with Fara, he begins to tell her his long tale, again, for the very first time.
The Legend of Zelda: Link's Awakening - This ending still makes me cry when I watch it. As the Wind Fish explains that the nature of all dreams is to end, Link plays the Song of Awakening that he learned from Marin. As the sad Melody plays, you watch as Koholint Island, and all of its peoples, fade away into nothing. Marin, whose fondest wish was to see distance lands and sing to the people there, continues to sing her song even as she disappears. If you got the best ending, you then see Marin with seagull's wings at the end, still singing her song...
Earthbound (aka Mother 2) - The nature of the ending here caught me completely by surprise. That only your prayers could defeat the ultimate evil, Gygas, was unexpected. But the part that blew me away was how, as all the people prayed, it had you, the player, also praying for the safety of Ness and his friends. That was so well done that it really moved me. Plus, at the end, you could go back through anywhere in the world, seeing what all the people had to say. And of course, at the very end, you got a nice little note from your other enemy, Pokey (who got what he deserved in Mother 3; a gruesome fate, for a gruesome man).
Final Fantasy IV - The original ending where everyone prayed for you, and which still (with that music, which FF12 stole) moves me deeply today. This was my first RPG, so I was quite heavily invested in all of the characters as I watched them resolve their issues (though it was often bittersweet) and finally move on towards a better world, as the moon left and began to search for its own light. Man alive I can't wait for the DS remake of this game to come out in America!!!
Metal Gear Solid: The Twin Snakes - Seriously, everything about this game was incredible. The themes were evocative without being heavy-handed, and the ending worked perfectly. Listening to Naomi start crying as Snake lied to her about Gray Fox's last words was incredibly moving, and hearing her talk about choosing to live for the future instead of the past as Snake and Meryl left the island was also well done. The capstone, of that beautiful Gaelic song to imagery of real Alaskan wildlife, couldn't have been more perfect.
I could go on for a while, but those are some of the best endings that jump to mind for me.