You might as well label this thread 'All Spoilers All The Time', so I'm not going to put in the tag.
The Dark Glory War by Mike Stackpole was like that. The heroes are the Heroes of their time; they're the PC's, the main adventuring party by way of comparison. It looks like they are going to triumph right up until the end, but they fail. The evil queen takes most of them and turns them into monsterous undead creatures, leaving only the viewpoint character to live and report what has happened so the human lands will despair at their massive, massive failure. Dark, upsetting, but still good.
Now, the sequel, the DragonCrown War series... good, up until near the end of the second book, where the main hero sacrifices himself to stop something bad from happening. I was... pretty shocked, but it was in character for him, and I could even see a way he could be brought back. So I eagerly start the third book... and it looks like that ain't happening. Not good, since I don't really like any of the other characters, period. I even did something I never do, and skimmed ahead at some points to see mention of his name. Nope, nothing. The book languishs on my shelf until I can get up the floon to start in on it again, and the series so far goes unrecommended to friends because of that. So, there are bad-bad parts to this idea as well.