Hmm. I've had some sad moments after finishing games like

Zelda - Links AwakeningLink woke up in the end just to realise that the whole event , the whole adventure, all the people that he met is only dream.. or a nightmare perhaps... maybe Koholint Island really exist .. Very good game

Final Fantasy Adventure (Gameboy)
I've lost count of how many NPCs had died throughout the game.... so sad..

Final Fantasy 6
Needless to say, the ending was superb...

Lunar Silver Star Story