Honestly, I don't think I've really been impressed with a game ending since I was 12 years old playing Metroid.
I keep running through game endings and come up with mediocrity piled on more mediocrity. Even games like Mass Effect, while satisfying, were still pretty meh overall. Which is probably why I enjoyed Halo 2's ending. I didn't see it coming and that sticks with me.
If I had to pick one, SotC is pretty damned good. Then again, that game is bottled genius from front to back.
MGS4 would have been great had it ended with the epic boss fight. All of Kojima's ramblings after that point killed it for me. I just wanted it to end. It was Return of the King-esque. The climax is done, the player is emotionally spent, and then the director/producer decides it's time to tie up every loose end strewn throughout hours and hours of gameplay/footage. It kills the moment.
Or check out my new webcomic: http://selfcentent.com/
