I like feeling accomplished after beating a game. Saving the world is a great feeling, even if it's only make-believe! I cannot think of any commercial RPGs with tragic endings off the top of my head, though the RM game Finding Eden
has a pretty tragic ending from what I remember. In that case the tragicness of the ending didn't take away from the game because it's a short, plot-driven game. Most of the God of War games and Zone of the Enders (not RPGs) have pretty tragic endings. With every tragic ending, whether be in game, book or film (but especially in a game), I think there always needs to be some pay-off. If the hero dies, the villian has to die harder.
One thing I DESPISE is when games tease the death of the hero at the end, only for them to ride by on a chocobo and everyone is like "Oh look! They're not dead! Hurray!" To me it just seems like a cheap attempt at suspense for like 5 minutes.