Both of mine have already been mentioned by others, but I'll say them anyway.
My two stories are Cremia and Romani and Link and Marin. I've already gone over why in other threads; these two stories are what I consider to be the saddest moments in Zelda history. Cremia tries to protect her little sister from the falling moon by giving her Chateau Romani to dull her senses, and then the spend their final moments together. I'm an older sister, and I understand Cremia perfectly. It is, in fact, the only story in any video game ever that's brought tears to my eyes.
The entire story of Link's Awakening is also heartbreaking, particularly with Link and Marin. I love Marin to death, and I always feel just awful at the end of the game. It's even worse when you play through while you know what's going to happen, and she tells you all of her wishes and dreams and everything, about how she wants to be a seagull and all of that...