I maintain that the championship stories that unfold in racing games like F1 97 or TOCA Touring Cars can be right up there if you play to the drama.
I get far too involved with my championship saves on TOCA 2. The Peugeot. Oulton Park. That Pole Position. The mayhem at turn one. The fightback. The failure.
Wouldn't have expected anyone to mention it before me, but try Mother 3. Quite brilliant all around with plenty of twists. And with it, a brilliant gameplay to go with.
Mass Effect 1 and 2. Both are great games with a far reaching story. I have played both of them countless times and am waiting for Mass Effect 3 to get here.