Legend of Mana has one particularly amazing, heart-wrenching story arc (the rest of it is amazing, too). The original
Metal Gear Solid is pretty great, too, but it also has a great cast of characters helping it along, too.
What? You want newer games? How about the Sam and Max games? Then again, they are also in a genre that lives and dies by having a great story, so they'd
better. Honestly, I haven't played enough current-gen games to really give an accurate representation. I think that
Saints Row the Third at least tells its story very well; it may not really be a very good story, but it's done up in a way that I consider fun and which keeps your attention very well. Well, okay, the original
BioShock was very good, too, in my opinion. It's a little typical, but arguably (at least the first time), the big twist is actually
completely unexpected.
I would not, however, say that
BioShock Infinite's storyline was great. It lacks -- and I mean this sincerely -- the subtlety of the original's telling. At several moments, I felt I should expect an "And that's terrible." out of nowhere. At least the twist that shows up in the ending is still good.
edit:
Spec Ops: The Line does, however, have an amazingly clever twist. As best I can tell, though -- the rest? Not so much.
Does this hack seriously believe that
Hotline Miami had a better storyline than
Chrono Trigger? Christ,
Pong had a better story than
Hotline Miami.