MGS
Soul Reaver

terranigma

half life 2

xenogears

final fantasy 8 sorry, i loved it.

chrono cross

resident evil code veronica, nice to see it mentioned here, great game, great story.

Ocarina of Time-Straightforward story, but at the time (and still today) its presentation of the story was legendary.
Kingdom Hearts Series-Convoluted as it is, it tries.

Paper Mario - The Thousand Year Door. nuff said

I agree that the basic plotline is fairly standard. Man likes Woman. Woman is comatose. Man strikes a deal with Demonic Entity and begins hunting ancient and extremely lethal colossal creatures across an unknown desolated land, despite overwhelming odds and the fact that every kill advances his marked physical deterioration, in order to wake up Woman.

What really makes Shadow of the Colossus special is the combined effect of many things. The lack of shoehorned exposition aimed directly at the player. The protagonist and more specifically, his clearly defined limits. (so he's not just a subject of someone's power fantasies) The richly detailed and carefully constructed world that feels more genuine and somehow more alive than those in most other video games, even though with the exception of Wander and his horse, the Colossi and a few critters here and there, it's completely lifeless. I think that's mostly because of how indifferent the world in SotC is to the player, whereas most game worlds make sure to pamper the player ad nauseam, to make sure that they're not bored, are not about to get bored, or won't miss any of the amazing story which the writers earned their paychecks for. Amusingly, it's far easier to lose yourself into the world in SotC and forget that you're really just playing a video game because of this fact. (not so sure about the PS3 version, though, with its obtrusive trophy notifications) Hell, there's not even background music while you're exploring the Forbidden Lands. (which is very advisable. If you're always just making a beeline for the next colossus, you're doing it oh-so-very wrong)

But maybe this is better suited for another thread, what I'm blathering on about here is probably a broader subject than what the OP had in mind with "story". I just think that with SotC, "the story" is much, much more than the characters and plot twists and whatnot. Besides, I'm a sucker for minimalist storytelling, so to briefly graze against the actual topic: almost all Metroid games not released on the Game Boy Advance (remember, prior to the 7th gen), and Shadow of the Colossus. There are definitely others that I'm just forgetting right now.

Aaaand somehow this post turned into another silly romanticising essay about Shadow of the Colossus. *laughs* But I guess there's a reason it gets constantly thrown around as ammunition in the pretentious "omg games are like SO totally art" threads that pop up every three months or so.