Alice: Madness Returns, Spec Ops: The Line, Assassin's Creed III, Batman: Arkham Knight, Mass Effect 2, Bioshock, Deus Ex, Dishonored, F.E.A.R., Star Wars: Knights of the Old Republic II: The Sith Lords, Ratchet & Clank 3: Up Your Arsenal, The Wolf Among Us, Sam & Max: Season 2, Halo 2/ODST/Reach, Heavy Rain, The Last of Us, Far Cry 3, The Simpsons Game, Grand Theft Auto IV, Portal 2.
There's probably more I'm forgetting, but of all the cinematic games I play, those are the stand-outs for a good story, a great ending, or both.