Well, in certain cases in fiction, having a heroic character achieve an ultimately tragic demise can be a good idea. It leaves the audience/readers with a tear in their eye and a real appreciation for all of the good the hero has done; whereas having them save the world, get the girl and live happily ever after just isn't as satisfying.
Look at Death in the Discworld series, for example - even when his saves the Disc from gloom and evil, he still just goes back to being the personification of death. He cares about the people living on the Disc and wants to help them, but even when he does help them, he can't have the glory and love most heroes get, because he isn't one of them. He's the Ultimate Reality, where everything eventually ends up.
I don't know, this is just my writer side rambling...