Chrono Trigger's story is what made it a classic in my eyes (though some people will call it generic or standard nowadays.).
For some reason, after I read the instruction manual, and finished the game, Zelda: A Link to the Past really felt powerful to me. I don't know why. There was hardly any narrative, to speak of.
For me, the end all/be all of great video game stories is Metal Gear Solid 3. No other game competes, in my eyes.