For me... well, I haven't played many games with good stories. Still, if I have to choose, it would be in Wind Waker, where SPOILER!!
You have to take Medli, the bird-girl from Dragon Roost Island, guide her through the deepest, creepiest, saddest temple in the game, where she'll live the rest of her life in prayer for Hyrule, eventually drowning when the ocean collapses onto the land.
The one who had a successful dragon taming/music career lying in front of her, born and raised to soar the skies with her own wings, is possessed by an ancient spirit, following which a stranger in green takes her to a faraway island, massacres his way through the place, and puts her in a featureless room deep under the ground/ocean, where she is to spend the rest of her life doing practically nothing.
The end of the game is quite sad too, where the magical land of Hyrule is drowned once and for all, its history going ad undas (literally "in the waves").
