Oh man how I have been waiting for this topic. The game that I personally think tells a story better then quite a few is Red Dead Redemption (aka RDR). RDR is the "sequel" to Red Dead Revolver for the PS2, which in a nutshell told the story of a boy's home that got attacked, A man killed his father and mother in front of him, and ends up severely hurting the man with The Scorpion and getting a scorpion scar on his right hand. But that game doesnt really matter much, as I never got past the first level :V
Back to RDR though, The game starts off simple: The goverment has tasked you with killing or capturing your old gang. You, John Marston, were once part of a gang that pillaged, raped, murdered, and all other sorts of crazy Old West things. You were taken in as a son by the leader of the old gang, which iirc his name is Dutch. Dutch and co. have been on the run for a long while, with the game hitting (part) of its climax when you "murder" him.
But wait, Why does John want to kill his old gang members? Truth is, He kinda does and kinda doesnt. He wouldn't just wake up one day thinking "IMMA MURDER THESE GUISE"; no, the goverment is forcing him to. How? By holding his son and wife hostage. You became a simple farmer after leaving the gang when you developed *gasp* MORALS. Once you split, the whole gang split. One of your old members, the worst shot of the gang who's name escapes me is hiding at Fort Mercer, first time you attempt to kill him ends you wounded (and nets you new allies), second time goes much better with his demise. After killing him, Irish (an ally who ISNT irish) decides to take you on a raft ride to Mexico, where the next member is hiding.
He gives you a decent horse, and then you hit the half-way point of the game; when
this badass songplays while you are riding to a mexican city to prep for your former ally's demise. I cant put my finger on it, But riding a horse halfway across mexico with this song playing is... Iunno, Emotional. Listening closely, It points out to you how far you've come, and how far away you are from the climax of the game. All the things you've done up to this point is only a fraction of what you will do - what
John will do. In a nutshell, the Mexico arc is you meeting an old gunslinger, getting your Dead Eye (aka bullet time) upgraded, side quests main quests oh my. Finally you end up killing your ally, who iirc is a Mexican army general; a corrupted one at that, stealing all the woman in mexico.
After killing him, You decide to go back to america via the bridge you fixed in a main quest, and head to West Elizabeth, aka the colonized part of the Old West. You meet the dude whos holding your family's lives by their throats and his goon Edgar Ross and Archer Fordam, respectively. After doing more quests, they finally decide to send you up north to a snowy moutain where Dutch is residing. Now heres where it gets really climactic.
You end up killing all of Dutchs goon's, and managed to stalk him up to a mountainside, where he gives a speech. The line that hit me most is "I cant fight my nature John. But its my nature... to
fight my nature. You see the paradox Marston? There's nothing guys like us can do: But fight the very thing that forcing us not to fight it.", or something along those lines. Instead of you killing Dutch, He decides to fall backwards off the cliff. After traveling down the mountain, you see Ross shooting Dutch's body, saying "It looks better on the resume." He then tells you you're free to do what you want, and you're family is back home safe and sound. Then
this song starts playing when you mount your horse. Everything you've done so far, everyone you've helped and saved, all leading up to this climactic moment of the game: the tearful ride home to your family.
When you get home, You see your family; John's family. I was in tears after I saw his family, as should most people. Everything you've done, every little god damned thing, And you finally get the Redemption that you've longed for. After meeting up with your son John Jr, your wife Abigail, and your... old man, Uncle. Uncle's just an old friend, getting drunk every night, though he's pretty good with a gun. After herding cows and other things any ranch does, Something happens. When you see this, you realize everything you've done was for nothing. Killing your old allies, helping out Mexico as a whole, Killing your "father" Dutch; all that was only the calm before the storm. Now the storm comes.
The army that freed you? Yeah, They want to make you their pawn again. This. This part of the game is the best, the moment of realization. Nothing is forever. Everything comes to a sad end, just like Madoka taught us. We can fight despair, But it's always there, and will always come back. After losing Uncle in a short firefight, John demands that his son and wife make it out of the farm ASAP. There was protest, but the final wave of the army is coming and they must escape. Abigail is crying, she doesnt want to leave John. John tells his son he's going to be the man of the house now; John knows whats about to unfold.
This is it. The last stand. Everything you've done, everything comes to an end now. John sighs, and sigh of despair, yet at the same time relief. He's redeemed himself, and theres nothing more for him to do in life. He opens the barn doors, and steps outside. Ross offers you if you want to, in short, become his plaything again. You aren't, and John damned well isnt about to. Entering dead eye one last tearful time, You aim to take down as many people as you can with your only gun; the Cattlemans Revolver, the first gun you've ever gotten. A dinky little 6-shooter, and sadly, Those are the last shot's of John's life. You remember to yourself the name of the joke that your son made before, when you taunted him for reading books: The Day John Marston Stopped Shooting. It was true in every sense. After taking down those 6 people out of 50, A torrent of bullets rain on you. You've been shot in every part of your body, There's nothing left for you or John to do. It's over, and it was a miracle you were even alive after all that lead. Ross turns away, disappointed that he wont get to abuse another human soul like he oh so lusts for.