The Longest Journey
Dreamfall: The Longest Journey
Mafia
Planescape: Torment (never completed it, got stuck fairly early on)
Still Life
Heavy Rain
Mass Effect 1 (I have 2, but I haven't been impressed by it thus far)
Red Dead Redemption
Uncharted 1+2
Vampire the Masquerade - Bloodlines
While this is not a huge list, it's really the only games I can think of that truly had a great story. I like all sorts of genres, besides RTS, so I'm willing to accept pretty much any recommendations if they have a really good story. While graphics aren't super important to me, I'd prefer (note: not required) that they look at least somewhat decent. Also as a note I've pretty much played all of the big name titles for the recent consoles except anything on the Wii.
Also for the sake of a discussion, and not just a thread to recommend me stuff, what is everyone's favorite story in a game?