Xenogears definitely has the best story in a video game IMO assuming (A) you like the premise and (B) you have time on your hands. If I played Xenogears now, considering my lack of free time, it wouldn't be nearly as enjoyable. Because I'd probably forget half the story in-between the sessions. I see some people bringing up Chrono Trigger. If there's any game that would really benefit from a remake - not just a port, but a full re-telling of the story - it would be CT. The limitations of the SNES system really hurt the story.
I just remembered another game that had a fantastic plot -- Deus Ex. I still haven't played the second one, but the original Deus Ex was fantastic. "The Last Express" was also pretty good. I heard that "Syberia" and "The Longest Journey" also had fantastic stories, but I never finished either one.
Now that I think of it, the Wing Commander games seemed to have pretty good plots from what I can remember (which, sadly, isn't very much, and might be influenced a bit by nostalgia). I'm thinking III and IV were both pretty good in that regard. I remember there being a pretty big twist in III that I don't think I saw coming.
Oh man did that game suck. This game had a great plot. http://www.juegomania.org/Leisure+Suit+Larry:+Magna+c*m+Laude+--+Uncut+and+Uncensored/preview/pc/9/9326_c/Caratula+Leisure+Suit+Larry:+Magna+c*m+Laude+--+Uncut+and+Uncensored.jpg
Holy turds, you're my new best friend! I LOVES me some Wing Commander, ever since the SNES days. But Mark Hamill brought it to another level. Colonel Blair FTW! Was the twist when Hobbes betrayed the humans? Oh, or the twist from 4 when the Admiral turns out to be behind the whole thing!
Wing Commander was indeed awesome. A nice attempt at a space epic. I remember playing the first one on a computer that had 8mb of ram and a 80mb hd.
<object width="425" height="355"><param name="movie" value="http://www.youtube.com/v/1-9my0tsutw&rel=1"></param><param name="wmode" value="transparent"></param><embed src="http://www.youtube.com/v/1-9my0tsutw&rel=1" type="application/x-shockwave-flash" wmode="transparent" width="425" height="355"></embed></object> <object width="425" height="355"><param name="movie" value="http://www.youtube.com/v/R_PqZofBPWg&rel=1"></param><param name="wmode" value="transparent"></param><embed src="http://www.youtube.com/v/R_PqZofBPWg&rel=1" type="application/x-shockwave-flash" wmode="transparent" width="425" height="355"></embed></object>
Nope. Wing Commander is made by an American company (I think it was Orion, who put out lots of good games back in the day), thus no poor English translations.