Id have to say the most recent game that did that was The Last of Us. That opening was unexpected and it hit like a truck. From start to finish, Naughty Dog really knocked it out of the park. Challenging my emotions throughout, challenging my skills as a gamer. The acting is the best Ive had the pleasure to behold in a videogame. The soundtrack fits it perfectly and damn, that ending.
The Last of Us is probably breaking into my Top 10 of all time.
Before this Id say probably something between a few other games.
Mass Effect had me gripped on the universe. It just did that futuristic, Sci-Fi space bollocks right. I cant quite explain it better than that. It just felt right.
Metal Gear Solid 4 was the game I wanted more than any other, ever. It probably blew me away primarily due to the hype surrounding release at the time, but when youre reaaaaaaally at the end of the story. When youre crawling through that damn microwave filled hallway and you know the end is just a few minutes away... It hits you. When youre fighting Ocelot, man to man, with those damn tunes playing from Metal Gear games of old, its just the top of the entire series. Simply incredible at the time, not sure how it would hold up today.
Valkyria Chronicles. Beautiful videogame with a brilliant cast of characters. Really didnt see the "hit" coming that blew me away. One of the very few times Ive ever had to hold back the tears... And any videogame that can make you do that is something special. Fell in love with it. Hate SEGA for refusing to put VC2 and 3 on PS3.
Quite a few more did it before this generation also. Metal Gear Solid 3 being the main culprit.