Heavy Rain - It fits the game, as this is a game made up of several "What if"'s. It is a depressing story where the player is trying to guide the characters to the happy ending rather than actually playing, so it feels more like you failed them rather than something cheap.
Halo Reach - This was actually one of my favorites. Up until the cutscene, you are Noble 6. Suddenly, when you are overwhelmed, Noble 6 becomes a character based on all your actions and thoughts while playing, and you are forced to watch him/her die. Up until the epilogue, you are hopeful since the screen fades to black in the middle of the struggle, although said struggle still seemed one sided. Then you get those dreams crushed instantly, and emotionlessly, and even though you know your actions in the end meant something for the game universe, it still doesn't feel all that better (which might echo a sentiment that families of fallen heroes have).
FFX - I started out not liking Tidus, but eventually came to respect him, not just because he worked well in most fights but also because in the end he made a near impossible choice: Save a world at the cost of his existance. Not life, but existance (yes in the end his soul/spirit/whatever turned out to be fine, but he had no way of knowing this beforehand). Plus, the world was going to be ungrateful for it, as the villain had pretty much rooted himself into every part of the world through a religion that controlled the economy and the sole source of entertainment. However, it was that or continue to let the world slowly get worse (and towards the end, there was also the fact that there was no known weapon against the greatest threat around). His death was treated right though, and it could actually be quite touching to watch.
Mass Effect 2 - Here's a great one, because since there's a "playable epilogue" if a party member died, their absence is very noticable. In addition, most of the time, their deaths were YOUR fault, which just adds to the sadness. Of course, you can kill Shepard if you do the wrong thing as well, which the ending makes very clear that you doomed the galaxy in doing so. Of course, this is a Bioware game, so I expect this sort of emotional scene if its going to have a death scene in it.