"I'm gonna show you and everybody else that Willy Loman did not die in vain. He had a good dream. It's the only dream you can have — to come out number-one man. He fought it out here, and this is where I'm gonna win it for him."
This character has a dream that can never come true.
This character has been hurt by the world. A lot. Yet despite their pain they never lose hope in their most cherished dream, and even draw strength from pursuing their most fervent desire. Sadly, it's a fool's dream, and not in the romantic Don Quixote sense. It's something that is not just out of reach, but that they are fundamentally incapable of achieving or receiving, and they are usually too emotionally damaged to realize this. They either can't conceive of it as such or avoid the realization because they subconsciously know it would give them a Heroic BSOD or drive them to despair. Because of this, the dream stops being something to pine for and becomes a tangible goal they seek, with uniformly horrifying results.
There are a lot of reasons for the dream being out of reach, and for failing to achieve it. Here are a few common possibilities:
They want something that is achievable by a normal person, but they are too emotionally unstable to make a well thought out go at it, and usually botch the attempt. For example, an ex-boyfriend who was dumped for being unstable might kidnap his girlfriend to try and get back together, or a villain who suffers Chronic Villainy being unable to get the townspeople to love him.
The dream is achievable... but you won't like the results of questing for it or accomplishing it. It can be a character after the "Well Done, Son" Guy's approval, but that approval is only given after the character does something tremendously damaging to themselves as a person or to another. Or realizing that It's Lonely at the Top and they Kicked The Wrong Dog in their ambitious quest for love, fame, and/or fortune.
The main characters of Wandering Son are preteens with gender identity issues. Shuuichi is a girl in a boy's body and Yoshino is a boy in a girl's body. Both take up crossdressing to try and emulate their preferred gender, but know deep down what they want isn't truly achievable and will only become worse as their bodies mature through adolescence. The trope is eventually averted.
This seems to be a major theme in Tsubasa Reservoir Chronicle, as a few characters are described as having dreams that they can never achieve. One dream in particular was the entire reason the story took place. Yuuko apparently died at some point and it became the Big Bad's obsession to revive her. Everything that happened was either part of his Gambit Roulette to revive her(something clearly stated to be impossible) or the multiple gambits designed to stop him.
Jellal's dream for freedom in Fairy Tail, even if that may sound wrong. He was a child slave. His best friend/possible crush staged a rebellion, developed magic, and overthrew their oppressors just to save him from torture. Rather than get to relish in his freedom with her, he was forcibly turned into a villain by the girl who would later pretend to be his underling. So now he's spent X many years of his childhood as a slave and spent eight brainwashed. His former childhood friend who once went to great lengths for him wants him dead. Her new friend seemingly helps her get her wish. When Jellal manages to come back he has amnesia and has genuinely regained his former nice personality, but gets to enjoy this for less than 24 hours before being arrested for the crimes he was deceived into committing.
Said underling was trying to go back in time and redo her life from the moment that it went horribly wrong. Eventually, the weight of all the ruthless acts she did (when thinking that she could just undo them by not becoming a dark time mage) crushes her psyche, and she ends up using a high-risk version of time travel to make things right. At best, it ages her into an old lady, at worst, she really is dead.
Manga-version Greed may have such a dream. Despite essentially being a parasitic entity formed of concentrated greed and thousands of tormented souls, his true dream is to have friends. It seems he subconsciously recognized the impossibility of this dream and so instead focused all his efforts on acquiring everything else to fill the great gaping emptiness he felt inside. One character commented that it was sad how he could not even remember his own dream.
Surprisingly, Father, the main antagonist of the story, was driven to the horrible acts he committed likely as a result of his own dream: to be free of his flask. Ultimately, the only method he could devise to accomplish that task was to carry out the destruction of Xerxes, and from that point on he would do anything to maintain and expand his freedom.
Greed's dream is even more tragic because he actually achieves it moments before dying for good. He seems a little surprised when he realizes that he cares enough for Ling to sacrifice himself to keep Father from absorbing him too. He is even more surprised when he sees Ling and Ed trying so hard to save him. He spends his dying moments saying goodbye to his friends.
Orochimaru of Naruto, moreso in the anime than manga. Orphaned at a young age, he cherished one dream above all others: To live long enough to see his parents reincarnated once again. Years of war and tragedy eroded his morality until the dream became twisted and all but unrecognizable. Sasuke pitied him for having so thoroughly lost sight of his goal.
In Bleach, Ichigo suspects this is Aizen's driving motive. Having strength and potential unrivaled by any other, Aizen always existed on a different level from other Shinigami. He searched for a comrade or rival, somebody to match his strength, but never could... and from the moment he gave up on that dream, he began his descent. Yet deep down, he still held onto a simple dream that ultimately led to his defeat - A dream of being a normal Shinigami.
Fate's only wish in the first season was to have her mother be the happy, smiling woman she remembered from her early childhood. Then she learns that in those memories, Precia was never smiling at her.
Reinforce of Magical Girl Lyrical Nanoha A's revealed in the third Sound Stage of StrikerSthrough a video recording that she had but one small wish: to live life together with her Meister Hayate and the Wolkenritter, gathering around the dinner table and sharing smiles with each other, a dream of peaceful days she could have had. Alas, her nature as an Artifact of Doom made this dream impossible unless a small miracle happened. It never did, and in the end, she had to sacrifice her life to save her Meister, stopping this dream from ever being fulfilled. However, a miracle did happen for Hayate's Knights, who shared Reinforce's dream were freed from said artifact and allowed to spend their final lifetime with their master.
SD Gundam Force has one of the most tragic cases. Deed, one of the Knight Gundams falls in love with the human Princess Rele. He betrays not only his commrades but all of Lacroa Kingdom in order to obtain what he thinks is the key to making him human like her. He eventually dies without ever even confessing his love for her.
In the Dead Moon Circus arc of Sailor Moon the Tragic Dream of the Amazon Trio is to have a dream of their own.
In episode 9 of Mawaru-Penguindrum, we learn that Himari and her friends Hibari and Hikari wanted to be Idol Singers, but Himari couldn't join them in the upcoming audition because of Chiemi's accident. After other incidents, Hikari and Hibari pressed on and became a succesful duo while Himari became an Ill Girl... the "red and blue" girls we know. Himari is sad about it, but she wishes the best for them.
Ironically, Princess Tutu and Princess Kraeheshare the same Tragic Dream, although it's tragic for different reasons: They both want Mytho's love. Duck/Tutu has the power to help Mytho regain his feelings, but she's destined to vanish if she ever confesses her love to him; it turns out that the only way for her to have him for her own would be to withhold his final heartshards. Rue/Kraehe, on the other hand, already "has" Mytho, but he's incapable of actually loving her, and when he starts getting his feelings back, he falls in love with Tutu instead. As much as she tries to convince herself that Mytho loves her, he only ever shows indifference to her, at best.
Viral of Tengen Toppa Gurren Lagann wants nothing more to have a family, which a) couldn't happen anyway, what with Beastmen's inability to reproduce; and b) given his immortality, not likely given that he'd outlive them.
Mr. Freeze in Batman devotes his life to curing his wife's terminal disease. His use of his company's cryogenic technology without their permission led to the circumstances that made him Mr. Freeze in the first place. And when he finally does find a means to cure her in the Lazarus Pit, his impatience twists her into the evil Lazara.
After an exhausting day being Batman, Jean Paul Valley reflected that after working for the Order of St. Dumas, which wanted to re-conquer Jerusalem for Christianity, and then being a Temporary Substitute for Batman, who wants to stop crime in Gotham City, he found the fanatical, obsessive Dumas to be the wiser man: sure, Jerusalem was never conquered again, but it was a tangible goal, one that could be achieved... stopping crime in Gotham is a madman’s dream.
In All Fall Down, any hopes the heroes or villains have about ever getting their powers back are moot.
Damon Drexl joined the Exchange, slaughtered his former crew, and even tried to take possession of the Artifact of Doom so that he could build a new galaxy without Jedi or Sith, but where rogues like him could run free of restraint. LS or DS, it doesn't end well.
Shadow AKA Channa Mae/Matilda/Sera Degana wanted to find purpose in her life; first by accepting the way of the Jedi, then by trying to save the innocents of the galaxy by joining Revan during the Mandalorian Wars. Only, she ends up completely losing her identity in her fanatical support of Revan.
Solomon himself is so determined to avenge his Padawan that he gets Drunk on the Dark Side. It's almost comical when said former Padawan is arguing with him about how badly he's fallen - speaking from experience! His niece, Telana, equally succumbs to fanaticism, believing the way of the Jedi is to "destroy Sith" only to have a force-deaf Channa Mae kill her in self defense.
The most tragic of them all is Akirakon Sin, his dream was only to serve the Sith King, the Sith of that era being more Proud Warrior Race Guy than Card-Carrying Villain. After the Rakatan invaders are driven from the planet, he and his entire Assassins Guild are imprisoned in the artifact by the Evil Advisor union, and the advisors begin the "Sith Lord" tradition of Chronic Backstabbing Disorder
RE-TAKE has Ghost-Asuka sees Shinji's dream (married to Asuka, with a child and living peacefully in some sort of Avalonesque utopia) when repairing his mind. Needless to say, not everything is...quite right. Firstly, the reason why Shinji's mind needs repairing is that just last volume, Asuka self-destructed her Evangelion to save Shinji. Secondly, Ghost-Asuka is bitter at Shinji for abandoning her in End of Evangelion and is repairing his mind so she can keep on punishing him. Lastly, Shinji is being possessed by Shinji from End of Evangelion, so this might not really be that Shinji's dream.
Subverted when Shinji has the chance to achieve his dream but he turns it away because it would doom the world he has saved -he says himself being so selfish would be unforgivable... and because if he took it, Ghost-Asuka would be alone.
Films — Animation
In Don Bluth's flop The Pebble and the Penguin, penguin Rocko's dream is to be able to fly. Somehow, impossibly, in a way that makes absolutely no sense WHATSOEVER, this trope gets averted.
Tai Lung wanted to be the Dragon Warrior, which didn't work out so well.
Subverted with Po. Everyone thinks his dream is impossible, given that he's a flabby panda. They figure out that his gluttonous habits are actually a great asset when he learns to weaponize them.
Monsters University revolves around Mike's dream of becoming a Scarer. It's a prequel, and he doesn't. He's just not scary, but on a more uplifting note, his skill sets and knowledge allows him to be treated like an equal to the Scarers. Still, the other movie shows he excels when the focus is switched to making kids laugh, so there's that.
Frozen: Olaf just wants to experience summer, above all things. There's just one hitch; he's a living snowman. Luckily, when Elsa finally thaws the Eternal Winter, she takes the time to create a personal flurry for Olaf, so he can continue living, no matter the time of year.
Films — Live-Action
A.I.: Artificial Intelligence: Robot Boy David was a kind of Replacement Goldfish for his adopted parents and their comatose son, and when their son recovers, they come to reject David. He believes that if he finds the "Blue Fairy", she will turn him into a real boy and then his adopted parents will love him. He keeps pursuing this dream even long after they're dead. Eventually subverted when aliens, the technological equivalent of fairies, revive him and reunite him with his mother, granting him his wish for one day.
In Falling Down, William Foster wants to reunite with his ex-wife and child for his daughter's birthday. This was unlikely before the movie started because of his violent and obsessive behavior. He was a good man, but events conspire to make him go pretty much on a rampage through LA on his way to the daughter's birthday party. Needless to say, it ends in tears.
The 2007 remake of Halloween (2007) has Michael Myers with a single goal post-escape: reunite with his younger sister, the only living family he has and loves. He's a The VoicelessImplacable Man, so he never articulates the dream, but he shows her a picture of them as children and becomes docile when she pretends to understand and goes to hug him. Of course, by that point he'd killed several of her friends and kidnapped her, making a "happy family reunion" the last thing on her mind. Cue him becoming homicidal again after she stabs him and tries to escape.
I Am Sam has a mentally disabled Sam raise his genius daughter as a single parent. Eventually, he loses custody of her because he's an unfit parent despite his love, and fights tooth and nail with the help of lawyer to keep her. He loses, but keeps visitation rights.
In TRON: Legacy, Clu's ultimate objective is to turn he Grid into the "perfect" system, which was a directive given to him by Kevin Flynn. Unfortunately, Kevin did not realize at the time that true perfection is unattainable, and that Clu's goal was ultimately doomed to failure. Clu's inability to create the "perfect" system eventually drove him to betray Kevin and to turn the Grid into a brutal dictatorship in order to optimize it, which just made things worse.
In Sniper Master Gunnery Sergeant Thomas Beckett has a dream of retiring from the Marine Corps and returning to his hometown and settling down to a quiet life where he can spend his days fishing in his favorite lake. When he tells his new partner about this, he is informed that the partner knows the area and it has seen major development in the years since Beckett left and is no longer an idyllic small town. Most poignantly the lake has been filled in and is now a parking lot. Beckett does not take this revelation well and it is one of the factors that leads to the two men turning on each other during a mission.
In Introducing Dorothy Dandridge, the black title character talks to her lover, white movie director Otto Preminger, about getting married and having a "houseful of boys". He wistfully calls it a "beautiful dream" and walks out during one of her performances, effectively ending their relationship.
Animorphs: Jake's weakness is his sense of responsibility: As long as he can, he feels he's got to at least try to save his brother Tom. Jake's entire motivation, right from the beginning, is to free his brother. As the years go by this becomes less and less likely. And it all culminates in the worst possible way: As much as it pains him to do it, Jake orders his brother's execution.
Star Wars Expanded Universe: To Mara Jade, the ship she got as part of Talon Karrde's organization represented freedom. She was extremely protective of it, not unlike Han Solo with the Falcon. When she sacrificed it in Vision of the Future, it was a huge deal to her. A little later, though, she looks back on that instance and doesn't quite decide that It's All Junk, but she sees it as representing both her wish for trust and closeness and her unwillingness to let anyone near. She sees that continuing on that path would never have gotten her the closeness that she wanted, and she has to find a new way.
"I wouldn't ask too much of her," I ventured. "You can't repeat the past."
"Can't repeat the past?" he cried incredulously. "Why of course you can!"
He looked around him wildly, as if the past were lurking here in the shadow of his house, just out of reach of his hand.
"I'm going to fix everything just the way it was before," he said, nodding determinedly. "She'll see."
He talked a lot about the past and I gathered that he wanted to recover something, some idea of himself perhaps, that had gone into loving Daisy. His life had been confused and disordered since then, but if he could once return to a certain starting place and go over it all slowly, he could find out what that thing was.
Jesus-parallel Michael Valentine Smith from Heinlein's Stranger in a Strange Land gets blasted with shotguns and literally shredded by a mob while trying to convince them to love each other.
Then again, considering it made him a martyr and popularizes his religion/philosophy, odds are more converts will join. It helps that it was outright stated to give the followers super-powers that made them nigh uncatchable/unkillable, near immortality, rejuvenation, super competence in whatever field they know, and they hypothesize that every other human will eventually have to convert just to "keep up"... his dream went from tragic to inescapable.
Completely averted. Mike has already been shown to be capable of causing guns to simply disappear. And, for that matter, people. The only way he can be killed is if he wants to die,, and he does so in fulfillment of his dream. Also, he's not exactly a Jesus-parallel although he's definitely messianic; he's explicitly the Archangel Michael reincarnated on Earth (well, Mars).
A major theme in Of Mice and Men is how everyone has perfect dreams, but no one seems to reach them. When it looks like George and Lenny have finally saved up enough money to buy their farm and achieve their dream, everyone is stunned. Then Lenny accidentally kills Curly's wife and George kills Lenny to protect him from the lynch mob. This leaves George alone and implied to be unable to get the farm after all, since Lenny was the one who kept reminding him to focus on the dream.
With Steinbeck being Steinbeck the trope is zig-zagged in that it was indeed possible for George to get the farm with Candy but chose not to because he gave up. Throughout the book it's mentioned that the ranch hands spend all their pay on booze and women because they don't have any long term plans, which is a Self-Fulfilling Prophecy.
Also a theme of The Grapes of Wrath. The Joad family travel to California to pick fruit and rectify their financial woes, but there is little work and what they do earn amounts to very little. Rose of Sharon's dream of the perfect family is crushed when her husband leaves her and her baby dies.
Fëanor and his sons have a dream like this in The Silmarillion. They want to regain the Silmarils that Morgoth stole from them, so they make an unbreakable vow to take them for any other creature who has them by any means. The problem is that the Silmarils are consecrated jewels that cannot be hold by someone evil, so their actions in pursuit of that goal (including three instances of genocide) disqualify them from ownership of the Silmarils. Their unbreakable vow trapped them: They are sentenced to search for the Silmaris forever, but never attain them, so the Silmaris can be held by any other creature that is not evil except their creator and his descendants. The two remaining sons find this out the hard way.
Dominic Flandry's Tragic Dream is the security of the Terran Empire. In his case he knows it is a Tragic Dream and by the end his only hope is that the fall of the empire will be delayed and that his efforts will make fewer people have to suffer from the empire's downfall.
In A Song of Ice and Fire, Theon Greyjoy realizes far too late that all he wanted was to be a true Stark, something he could never be.
The protagonist of The Man Who Sold The Moon is a capitalist whose greatest dream is to the be the first man to walk and possess on the Moon. He manages to gain technical ownership of the Moon, but is forced to sell most of his rights to raise capital for his company. With those funds he manages to reinvent space travel, but cannot make the maiden voyage when there is only room for the test pilot, who becomes the first man to walk the Moon. Despite this he still wants to travel to the Moon... but as he stands outside the launch site his business partners inform him he's too valuable to the company to risk, and so he cannot leave Earth.
Gone with the Wind: Scarlett loves Ashley and wants to be with him. However, Scarlett is a cruel, self-obsessed Manipulative Bitch, and she is simply incapable of realizing that. This is why she and Ashley never get together, and forms a turning point in Scarlett's Character Development- near the novel's end, she realises that she only wanted Ashley because she could not have him, and that her lack of compassion has driven away everyone she ever cared about.
Shiro Kanzaki from Kamen Rider Ryuki initiates the Rider War which promises a single wish for the lone Rider who is able to survive the twelve other participants. In truth, Shiro has a proxy in the Rider War whom he intends to win so that he may have the wish for himself. His wish is to save his sister Yui, who is destined to die on her 20th birthday. He never succeeds in this despite it being implied that he has reversed time multiple times in order to achieve the desired result, never mind that Yui is resolved to sacrifice her own life if it means it will stop Shiro from condemning twelve other individuals to their deaths.
The Eleventh Doctor of Doctor Who had a dream to finally get home to gallifrey after saving it from destruction. In his last adventure, he is literally feet away from finally getting there, but he cannot get to them or the war they were locked out from in the first place will just restart. He tries for centuries to make the area safe enough to release them, but he regenerates and loses his opportunity despite finally making the area safe to bring them back. Though his future incarnations might find another way, the man who started it all will never get home.
One strip of The Far Side shows a chicken's dream of flight.
Religion and Mythology
The ultimate goal behind everything that Óðinn tries to do is the prevention of Ragnarök. Unfortunately, it's fated to happen, and in Norse Mythology, You Can't Fight Fate. Played with in that he knows that nothing he can do will avert the world's doom, but he fights it anyway because that's his fate.
In the myth of Orpheus and Eurydice, Eurydice dies of a snake bite and Orpheus travels to the underworld to bring her back. Normally, Hades keeps the dead forever, but Orpheus plays his harp so sadly that the God of the Dead agrees to make an exception, provided that Orpheus can walk out of the underworld with Eurydice behind him, not looking back at her until they escaped. Yeah...
Every Darklord that rules one of the Domains of Dread in Ravenloft has one of these which underpins their curse. For example, Count Strahd von Zarovich is always trying to win the love of Tatyana, the woman he murdered his brother for in his Act of Ultimate Darkness, or one of her reincarnations. The Dark Powers that govern the Realm of Dread placed the curse upon the Darklords to punish them for what they've done, and a combination of their intervention in events and the Darklords' own unwillingness to admit that what they did to get cursed in the first place was wrong ensures that no matter how hard they try, they will never achieve their Tragic Dream.
Willy Loman in Death of a Salesman. His life's dream is to succeed as a salesman and to be well-liked - but he was never cut out to be a salesman. The only reason he wants to be a salesman so badly is because some other guy that he loved and respected was one. He is atrociously bad at it. It's implied several times that had he become a carpenter, he could have been happy, but Willy rejects his own talents as being worthless. The play itself is set at the end of his life, and it explores what happens when someone must face that their dream is impossible.
Arthur Miller's essay "Tragedy and the Common Man" explores this trope as he used it in this play, and says that actually what makes it so effective is the insistent belief (by the author and audience as well as the character) that the tragic dream is possible:
The possibility of victory must be there in tragedy. Where pathos rules, where pathos is finally derived, a character has fought a battle he could not possibly have won. The pathetic is achieved when the protagonist is, by virtue of his witlessness, his insensitivity, or the very air he gives off, incapable of grappling with a much superior force.
Pathos truly is the mode for the pessimist. But tragedy requires a nicer balance between what is possible and what is impossible.
In Sweeney Todd: The Demon Barber of Fleet Street, Mrs. Lovett daydreams wistfully of marrying her dear Mr. Todd on happy holiday by the seaside. The lurid, unsettlingly bright colors of the sequence and dream-Todd's visible disinterest only serve to underscore the painfully obvious fact that Lovett, after what she's done for love, will never dip her toes in either blessed matrimony or the old briny. Instead, she gets thrown into her own oven to be burned alive after Sweeney kills the Beggar Woman, takes final vengeance upon Judge Turpin, and then, too late, finds out that the Beggar Woman was none other than his wife, whom Mrs. Lovett did not inform him was still alive because she wanted him for herself.
Sweeney's initial dream to be reunited with his wife and daughter pretty quickly goes to heck when he finds that his wife poisoned herself and then that he accidentally killed her and Johanna was in the clutches of Judge Turpin. Interestingly, he probably could have rescued Johanna, but saw it as too emotionally painful to be with her because she would either not remind him enough of Lucy or be too much like Lucy.
In Little Shop of Horrors, Audrey wants nothing more than to leave Skid Row and live a happy life in the suburbs (somewhere that's green) with Seymour. Unfortunately she is eaten by a plant, and that becomes her "somewhere that's green"
Final Fantasy VII: Sephiroth just wants to be reunited with Mommy (oh, and to become a god.) Jenova isn't really his Mommy, just an insidious Virus. Sephiroth doesn't even know about Lucrecia, and by the time he figures out what Jenova really was...well, he decided to become the next version of her as the original One-Winged Angel.
Selvaria wants to cook a meal for Johann in the Valkyria Chronicles DLC. She dies before she has a chance.
Claudia from Silent Hill 3 commits murders and tortures a little girl horrifically hoping to bring Paradise to Earth.
Mithos Yggdrasil from Tales of Symphonia wants to bring his dead sister back and fulfill their original dream of elimiating discrimination against half-elves. To this end, he divides the world in two and keeps both halves in Medieval Stasis for four thousand years while using their populations to make Powered by a Forsaken Child style Metaphysical Fuel so he could eliminate discrimination. And on the side, he tries to bring her back by keeping her spirit in the mana seed and creating an evil religion that sacrifices and experiments on countless people in order to get her a body compatible for her use. It gets to the point where the two of his original True Companions, Yuan and Kratos, are disgusted and secretly start plotting against him. By the time he manages to succeed, his revived sister who has seen everything from the mana seed is understandably upset and tells him to stop his evil ways before returning back to the dead.
Nessiah of Yggdra Union and Blaze Union wants revenge on the racist, classist society that mutilated him, made him immortal, and cast him out for attempting to assert his own free will. From the same series, Gulcasa wants to save the world and rebuild society from the ground up so as to protect the peasantry from the abuses of nobility and rulers—and in doing so, preserve the ideals that he and his surrogate mother Siskier fought for. To this end, both of them have become something of Well Intentioned Extremists—Nessiah, believing himself too weak to do anything on his own without his original power, decided to manipulate the world into providing him power; Gulcasa decided that the best way to get rid of the old system would be to depose its rulers and conquer the world. Over the course of their canon timeline, both of them lose everything they ever loved as indirect consequences of trying to make their dreams come true, then die horribly. Gaiden GameYggdra Unison gives them hero privileges and another chance—and it turns out Nessiah's dream was still doomed from the start. Gulcasa, however, actually makes his ideal world a reality and is fully appreciated for his efforts.
The Legend of Zelda: Tingle just wants a fairy of his own. Or to be a fairy himself. It's not always 100% clear.
The dream of curing Yonah and living with her is the only thing that keeps Nier going. Which is exactly what the Shadowlord, his original soul, also wants to do with his Yonah. Unfortunately it's mutually exclusive with the Shadowlord's dream because the precursors didn't anticipate that the Replicants would develop souls on their own, so curing the disease by forcibly reintegrating the original Gestalt souls with the Replicant bodies would effectively Mind Rape the Replicants. In the end, neither gets what they want. Nier discovers to his horror that the Shadowlord went ahead with the integration, so while Yonah is healthy, she isn't his Yonah. The Shadowlord discovers to his horror that Gestalt!Yonah is too compassionate to let Replicant!Yonah suffer for her sake, so she commits suicide to free her.
In Pool of Radiance: Return to Myth Drannor, the Big Bad tricks the baelnorn into helping her evil scheme by claiming that doing so would restore Myth Drannor to its former glory. He eventually realizes his folly and urges the heroes to stop the villainess. The past the baelnorn cherished is gone forever, but the future can still be saved.
Merrill of Dragon Age II just wants to recover the history of her oppressed people—particularly to fix the Eluvian. Unfortunately, the lengths to which she's willing to go for this—Blood Magic and deals with demons—have terrible consequences for her and others around her, and she isn't as capable of dealing with the danger as she believes herself to be.
Final Fantasy X has one, where Tidus slowly falls in love with the Summoner Yuna, joining her on her quest to defeat the Big Bad Sin and telling her about the places they'll visit after they're done. Except that the Big Bad can't be defeated, only sealed away at best, and doing so requires the sacrifice of someone close to the Summoner and the Summoner him/herself. Tidus is the only one not aware of this, and does not take The Reveal well. In this case, it's averted by the party having discovering the Religion of Evil behind Sin, and defeating it once and for all by attacking the God-figure that keeps regenerating it. This leads to another Tragic Dream since Tidus himself is a construct like Sin, and will dissolve along with it.
Saber (Fate/stay night) wants to redo her own backstory so someone more fitting can take her place in history. Not actually possible, despite the idea of the story implying it is, and merely a debate over whether its the right thing to do or not.
Ilya (Fate/stay night) wanting to be a family with Shirou and Kiritsugu, which she herself realizes is impossible.
Satsuki (Tsukihime) just wants to hook up with Shiki and is the only one from his normal life who understands something of his true nature. When she actually thinks she can connect to and win him, she has become a vampire with a growing taste for evil. Shiki can't be with her if she sucks blood and kills people, and she can't stop or she'll die. Satsuki's plight has reached memetic levels in the fandom... "Isn't it sad, Sacchin?"
Shiki (Tsukihime) has a literal tragic dream in which he meets with his recently-deceased adopted brother, discussing their relationship amiably while drinking under the moonlight. Unfortunately, this could never have happened outside of the dream, as SHIKI was insane and murderous even in his death and had been brainwashed into completely hating Shiki. Indeed, Shiki himself surmises that the figure he's talking to was created from his own mind to comfort himself, but the other guy insists that he's been released from his insanity after death.
Shiki's adopted brother himself also had a tragic dream, wishing to rejoin the Tohno family. SHIKI's insanity prevented him both from rejoining the family and realizing that doing so was impossible.
Shirou (Fate/stay night) gets a lot of flak for his ideals in the Unlimited Blade Works route, which not only are impossible to achieve but also will eventually betray him, leaving him bitter and broken. Shirou remains firm, however, because those ideals are still beautiful and worth striving for.
In the alternate routes, his adherence to his ideal becomes less strict. In Unlimited Blade Works he accepts that the ideal is impossible, but will still strive to follow them as best he can. In Heaven's Feel he abandons the ideal entirely.
Archer (Fate/stay night) lives only for the slim chance that he'll be somehow able to return to the past, find his past self, and kill him in the off chance this will cause a Temporal Paradox that will undo his own existence. He himself states that it's impossible because as a Heroic Spirit he exists outside of time and has become a distinct entity from his past self, but is willing to go through with it anyway on the microscopic chance he might be wrong.
Kiritsugu (Fate/Zero) is the one that inspired Shirou to follow his ideals. He lived his entire life fighting and killing, always striving to save people and all it got him was losing his adoptive mother, his wife and daughter and becoming a broken man after the end of the Fourth Grail War
In Hakuōki, Kondou Isami, a farmer's son, dreams of becoming a samurai and achieving glory as a general, despite the highly classist culture of the period of Japan's history in which he lives. His friend and fellow farmer's son Hijikata Toshizo shares his dream of being a samurai and hopes to uplift Kondou to glory as the leader of the Shinsengumi... but just as the group has begin to achieve some real legitimacy through their service to the Tokugawa shogunate, the country's balance of power shifts away from the shogunate and back to the imperialist Choshu and Satsuma domains that the Shinsengumi have been fighting against, and the onset of Westernization renders the samurai class obsolete.
From The Order of the Stick, Elan's dream to redeem his father and brother and eventually bring his family back together is what pulls the wool over his eyes in the Empire of Blood arc. He has to reject this dream and move on in order to save himself and his friends. In fact, if he never had a Tragic Dream that he was capable of rejecting, the group may never have survived.
Doctor Horrible's Sing-Along Blogplayed with this. The Title character has two dreams, both completely possible on their own. Join the Evil League of Evil and impress Penny. The tragedy is that he goes too far before he realizes he can't do both. No sign of Penny / good I would give anything / not to have her see...In the end, his attempt at being a supervillain costs Penny her life.
In Plague and Treachery on the Oregon Trail, Sarah Jane is an ugly girl with nasal passages that don't work, causing her to have a horrible voice. Even so, she dreams of becoming an opera singer and her family doesn't have the heart to tell her it's impossible. This is Played for Laughs at first, but after some Character Development amongst everyone, it became a genuinely depressing dream.
We are the last honest critics left in a culture of blind encouragement, telling people they genuinely suck at something before it sparks into a life-long passion. We are the only ones brave enough to be ambition abortionists and we end up saving everyone a lot of trouble down the road.
The online animated short Kiwi!, with a particularly sad denouement.
In Avatar: The Last Airbender , Zuko's quest for "honor" by capturing the Avatar is regarded this way by his uncle — unlikely that Zuko will ever capture Aang, or that Zuko's father Firelord Ozai would accept Zuko back even if he somehow succeeded. As Uncle Iroh puts it, it's an impossible quest, but it still serves a purpose because it gives Zuko hope. Then, when against all odds Zuko's dream finally comes true, he discovers it's not what he wanted.
Batman: The Animated Series: Mad Hatter developed mind control technology only to be with his crush, Alice. But as Batman notices, she is nothing but a doll. Mad Hatter doesn't take that revelation well.