Oooh. I'll go for a top 3.
1. Bioshock 2 "Redemption" Ending (Mentioned above, the Sad ending :<). I didn't get it myself (I ended up with the Good ending), just looked it up, but even so it was -incredibly- emotional. Almost cried when it ended, and seeing how I haven't done so for any book, movie or game since forever, that's saying something.
2. Halo: Reach. Great use of Gameplay and Story Intergration. In that ending, you end up actually feeling what Noble 6 feels like. Your only remaining goal is to survive, but even that's impossible. So you go down fighting.
And, hmm. My other spot goes to...
3. Tough choice, but I'm going with Mass Effect 2, counting the entire Suicide Mission as the ending. If you do well, it's exhilherating, with a ton of tense moments and a massive sense of satisfaction when you're done. Especially the Paragon ending. Or if you get the worst ending, and you watch the character's you've gotten attatched to die one by one (due to YOUR mistakes), until YOU die. I loved it. (I got the perfect Paragon ending on the first run, of course)