Red Dead Redemption

https://www.youtube.com/watch?v=VU1vbPbm9r4 

The ending to Rockstar's much adored western tale is quite fitting, given the treacherous setting of the game. Despite struggling through the entire game, overcoming all sorts of crazy odds and deadly gunfights, the game's hero, John Marston, eventually succumbs to the nasty Edgar Ross. After saving his family, and knowing he'll never see them again, Marston confronts his killers, only to be cut down by gunfire. It's a pretty gruesome death scene, and one that's very well delivered. No dialogue is needed, just Marston's last few breaths.
Jack, out to get vengeance for his father's death, tracks down Ross years later, and kills him in a duel. The game then shows what happened to the rest of the story's major characters.
Killing off the main protagonist of a several hour long story is a risky way to end a game, and unless it's done right, it can be easy for such an ending to fall into the cheap shock category, and an easy way to fake an emotional connection. Red Dead Redemption, however, did things right. John didn't die in a selfish or mundane, action way, he died defending his family, in a story that unfolded to this point very well, in a believable manner. You genuinely feel moved by Marston's death as he gives his all to ensure the survival of his loved ones, and as Jack, you also get to seek revenge. A great ending.