Another World, this story is goood.
The Lucas Arts games: Age of the tentacle, all the indiana jones point and click but the best is the fate of atlantis, secret of monkey island.
Half Life, Half Life 2, Portal.
Starcraft,broodwars, Stacraft 2
I think one of the stronger in game stories for me was in Mass Effect 1. The story tied in beautifully with the Paragon/Renegade system the game offered up. Even the primary antagonist Saren could essentially be scene as playing the "Renegade" part. Great game, great universe, great story. While the subsequent games improved the gameplay overall, they failed to deliver on the promising start of the original game as far as narrative. The third game especially created far too many narrative problems when viewed as part of a series. But the original remains one of the strongest I have ever played.
As others have mentioned however, Planescape: Torment is the best game I have played in he story department. Just a fantastic release from beginning to end.