This is based on opinion. Please don't list it on a work's trope example list.
Tear Jerker / The Legend of Zelda: Skyward Sword
The entire prequel manga to Skyward Sword, telling the story of the very first Link and how both Skyloft and the legacy of the Hero came to be. After being wrongly imprisoned for four years by his own country, he still takes up his sword when they free him to save them from the demons. He goes on to fight an unwinnable war in the name of the Goddess Hylia, uses the last of his strength to send Skyloft into the sky with the Master Sword, and bleeds to death all alone on the abandoned surface as he watches his home and all of his friends rise into the skies.
Link: My people, I pray...for your happiness. I wish I could go with you... but my body is heavy...
This is seemingly the only timeLink cries in any Legend of Zelda Game.
Made more emotionally painful when you realize; He and Zelda have been friends for years and has been searching for her the entire game, effectively making her Link's Living Emotional Crutch throughout his whole journey. Once she's been imprisoned, Link suffers an absolute Heroic B.S.O.D. and just completely breaks into tears, as he very well knows that if he fails, he'll never see Zelda again. The only thing that has kept him going through his adventure, the only thing that's mattered to him, has just been taken away from him. Damn.
At least Zelda's sleep wasn't permanent. Fi wasn't so lucky.
Younger Impa being left behind to watch over Demise's spirit and the Master Sword. With this music being played, it can bring grown men to tears. note It's also the music which plays straight after Demise's defeat, which makes it a Crowning Moment of Awesome for the player, at first..
Impa being revealed as the Old Lady who's helped Link throughout his journey, moments before she Disappears into Light. Especially since Zelda and Groose had gotten so close to the different versions of her over the course of the game.
The Skipper's Place in the Sandsea. It, like the rest of the desert, is old, worn-out, and rusty. There are a pair of broken robots sitting there (probably his family), never to move again, and Fi points out from the pictures on the walls that the Skipper had many happy, fun-filled days with his crew.
You're gonna start bawling like a baby upon reading a note on the wall of his home that said "Dear Dad, Good luck at work, vrrm!"
Lanayru in general has a ton of Fridge Horror that belongs here. It's easy to forget sometimes that nearly everyone there is dead, and by the sound of their stories, didn't Ascend to a Higher Plane of Existence. Even the ones you help are doomed the moment you switch to a different Timeshift Stone. Oh, and the Skipper at least seems to know it.
Also, what happened to Lanaryu is known as desertification, and it actually happens in real life, often due to destructive environmental practices in modern times. And lakes drying up into deserts? Also happens in the real world.
Later in the desert, you find the Thunder Dragon's lifeless skeleton. Activating a Timeshift Stone reveals that he was suffering from an illness, and knew the fruit of the tree that could cure him couldn't grow in his region and took centuries to mature, so he wouldn't live long enough for it.
If you wanna feel better, activate the Timeshift Stone after you get what you need for him. The skeleton isn't there anymore, indicating you changed the future for the better. You only get to do this twice in the game, and the second time is rendered useless.
If Scrapper is any indication, then all of the robots could probably be repaired with Ancient Flowers, but it still doesn't help with the fact that outside of very small patches under the effect of a Timeshift Stone, the home they knew is long gone.
More Fridge Horror: When looking for the keys to the Lanayru Mining Facility, you'll come across a few abandoned factories. At the entrance of the factories, you'll see notes from the foremen saying to keep Lanayru green. And, as you can see, they failed to do so...
Fi's farewell. "May we meet again in another life."
Many of the sapient species in general, particularly the wholly-unique-and-not-potentially-ancestral-to-known-species Mogmas, have a general air of tragedy about them for the simple reason that we know they're conspicuously absent by the time of the next chronological game, Minish Cap. At worst, they're doomed to suffer the fate of the Ancient Robots and only we know it, at best they're all going to be forced from their homeland, and whatever horrible thing is going to happen to them, it isn't that far off, only a few centuries at best. No sign that they ever existed will remain.
A likely minor one for Groose. Sure, he was a bit of a jerk to Link at first, but he eventually changed for the better. He knew his role wasn't that full of action, but he was all right with that. He didn't do much fighting, but he was important for Link to do what he needed. And yet, in all the games in the series, and all the years they span... it seems nobody remembered Groose's actions. Not only that, it seems he didn't have his own bloodline, like Link and Zelda do. To be able to play a role in shaping history, and not be remembered for it... it's a bit upsetting.
This could, however, be turned into a Crowning Moment of Awesome for Groose. He didn't have the blood of the hero like Link, or the spirit of the goddess like Zelda. The part he played in all this was purely his own. In a way, that is far more awesome than being chosen by destiny. It still doesn't really help that his (undeniably important) role in the story has been all but forgotten by history, though.
Though the Wind Tribe in Minish Cap ALSO had red hair and lived in the Cloud Tops, which has been stated to be what remains of Skyloft. Maybe Groose ended up better off than anyone could have imagined.
Of course, not being reincarnated like Link and Zelda means Groose escaped the fate of being reincarnated over and over to fight in an endless battle for all eternity.