Sword of Mana pretty much becomes just a long, painful series of Tear Jerkers from about mid-game, starting with the death of Medusa and working its way along the story, to the point where even the side-story of a comparatively random enemy boss gets somewhat sad as she tells the heroes how she succumbed to a sort-of corruption and thanks them for putting her down.
Just the way that the tree works... The goddess of the people's prayers is actually long since dead and the world is instead sustained only by the sacrifice of the two innocent mana girls that took her place. The heroine had to give up everything and it's implied in that the hero will stand guarding her as the last Gemma knight for eternity, constantly by his love's side but never able to truly be with her.
Legend of Mana. Each set of quests (Faerie, Jumi, and Dragoon) have their moments. For the Faerie, it's finding out that even though they're both dead and aren't bound by the rules of their lives, Matilda and Irwin still can't be together. In the Dragoon, it's learning that Larc, being bound to Drakonis, can't leave the Underworld despite Drakonis being defeated, and Sierra has to wait for him for a century. And the worst is the Jumi quests: Pearl is a false entity, every Jumi you meet in the game dies tragically, and when they're resurrected, you cry for them and turn to stone. The following cutscene, however, combines this with Heartwarming due to the music and the Teardrop Crystal. "I'm ba-ack!"
Let's not forget Final Fantasy Adventure/Seiken Denesetsu...when GIRL says "Bye" to BOY and has to become the last Mana Tree because stupid Julius took out the previous Mana Tree.
Hawk's and Lise's beginnings in Seiken Densetsu 3. All of the player characters have clear and compelling motives, but these two get the most effective Player Punches.