My absolute favorite game story? The Longest Journey.
I've played games with 'better' story than The Longest Journey, Planescape: Torment for example, but none I've ever played have had such an emotional impact nor the level of empathy I felt for the characters as in The Longest Journey.
The dialogue is perfect and the voice acting spot on for the main character, creating probably the most sympathetic 'hero' in gaming; April Ryan. Playing through the game you get the feeling you're enjoying a particularly good book or movie. Sure, it's cheesy in places, and as a game there are more than a few faults (The puzzles are some of the most ridiculous in the industry!) but the story more than makes up for it!
Now if only Ragnar Tornquist would continue the series. It still feels incomplete, and for a series with such beloved characters, it's terrible to not know how things eventually turn out.