Has too many to count, but a particularly cruel one happens to Tony Almeida on Day 3. After an agonizing 4 or 5 hours of knowing his wife Michelle is trapped in the hotel where a fatal virus has been released, he learns she's immune and breaks down crying in relief. As Michelle is on her way out of the quarantine zone she's taken hostage by the Big Bad, who immediately forces Tony into a Sadistic Choice between betraying all of his colleagues or letting his wife die after all.
Day 3? If anything you'd think the first finale had to be the most infamous/cruel one in the series. Think about it: after enduring hell for the past 24 hours, Jack has finally killed the Big Bad of the season, gotten Nina Meyers arrested, and reunited with his daughter Kim. Then he heads into the room where his wife is being held... and breaks down upon discovering that Nina had shot her minutes before and she's bled to death. No, Jack. There isn't going to be any happy ending for you.
Day 5: Lynn MacGill seems to think he might not have made a Heroic Sacrifice after all after he stops the gas from killing everyone in the building, but it just took the gas a few seconds to reach him, and he and the man guarding him die horribly.
Day 5 again: Evelyn Martin and her daughter are rescued by Jack, only to be murdered offscreen a short while later.
Day 8: CTU's attempt to rescue President Hassan falls through when they find out the video feed was prerecorded and he had been dead for some time.
Day 8 again: Jack decides he's done with CTU and he and Renee Walker finally resolve some serious UST. Renee gets shot in the stomach and dies. Jack doesn't take it well, to say the least. Jack's despairs all the more because Renee's killer could have shot her in the head, but went for the stomach just so that she would die slowly and painfully - and from an out of universe perspective, to give the audience a glimmer of hope that she would survive.
Day 9: At the end of the previous hour Big Bad Cheng Zhi has an assassin pin Jack's old flame Audrey down with a sniper rifle and threatens to have her executed if Jack continues pursuing him. CIA agent Kate Morgan manages to successfully infiltrate the place that Audrey's being held hostage in and kill the sniper... only for a second hitman to show up moments later and get a lucky shot off, killing Audrey.
In the Agents of S.H.I.E.L.D. episode "Making Friends and Influencing People", Whitehall has former SHIELD agent Agent 33 imprisoned in a machine, brainwashing her into serving HYDRA. While Whitehall is giving a speech, Agent 33 manages to get a hand loose... only for Whitehall to spot it, and causually resecure her, without even breaking his speech. Given that the brainwashing is meant to break the prisoner so they can be reprogrammed, it's likely this Hope Spot was a deliberate part of the process.
Wesley's final battle in the finale might be another example; the demon has him beaten with magic, so Wes resorts to a knife. The demon then blocks him, grabs a bigger knife, and proceeds to fatally wound Wes.
There's a great subversion when the demon tries it again. Unfortunately for him Illyria is quite able to kill him with the one free punch he offers her.
And let's not forgot Wesley's hilarious "I think we're winning!" in Over the Rainbow. Cut to shot of the good guys tied up.
The second fight with The Beast has two. The first is when Wesley's shotgun blasts bring it to its knees, with him advancing on it firing from closer and closer - until he gets within range, at which point it grabs him, hurls him into a wall, and stands up as though its apparent injury was a just an act. The second is when Angel puts his Game Face on and goes in for a rematch. He also manages to knock The Beast to its knees, produces a stake, and stabs it towards The Beast's eyes, the only seemingly vulnerable part of it. He stops an inch away, and the camera reveals The Beast had caught his wrist. Then it stabs him in the neck with his stake and hurls him off the skyscraper.
In "Intersections in Real Time" a captured and tortured John Sheridan refuses to be broken and is told he will now be sent to his execution. Tossed onto a gurney, he is wheeled down a hallway with a man of the cloth giving him Last Rites. When he gets to the room he is set up in a chair as the whole torture starts over with a new interrogator saying the same thing as the previous one did on his introduction. Death is what was hoped for, but even that is denied in this place.
Battlestar Galactica (2003): The midseason finale of Season Four. The humans and Cylons have put aside their differences and found Earth together. Cue exulting music. Oh wait, Earth is a nuclear wasteland and has been for two thousand years.
Blackadder: In the Blackadder Goes Forth finale, there's a Hope Spot when they're in the trench ready to go over the top and the guns go silent. Then:
Darling: Thank God — we lived through it — the Great War, 1914 to 1917...
Blake's 7: The entire final episode. After two seasons of searching, the loss of virtually all the original crew, the Liberator, and most everything else, the crew finally locate Blake on Gauda Prime. But put Blake and Avon in the same room, and all goes to hell in short order.
Cory: You just love dangling that little string of hope in front of us and yanking it away, don't you?
Mr. Feeny: I had a cat.
Later Played for Drama in one season 6 episode. Shawn's father is back in town, suffers a heart attack and after much drama and anguish the man promises his boys that he'll stick around from now on and they'll be a family. And then he dies.
For Walt, but not for audiences, in "Ozymandias." After realizing that Skyler and Walt Jr. had truly turned against him, he proceeded to kidnap the one family member he didn't believe could turn against him because she supposedly didn't know anything—his infant daughter Holly. But on the road, Walt sadly discovered that he was wrong when she started crying for "Mama."This made him realize he had gone too far. And it was still a terrible thing to do, even if it didn't turn out to be in vain and Holly hadn't turned against him.
Buffy the Vampire Slayer: The episode "Help", where Cassie (teenage girl who has had a premonition of her own untimely death) is saved by Buffy from being killed by a demon... but then a lethal trap goes off... and Buffy saves her just in time for a heart attack to kill Cassie.
CSI: During the multi-part episode of "Grave Danger", there's a really cruel and clever one of these. At one point, the CSI's think they've found where Nick has been Buried Alive. They start digging, and at exactly the same time, Nick can hear a scraping sound from outside the box. The team get really excited when they realize that the thing they're digging up a plexiglass box, and Nick starts banging on the box and calling out to them, clearly ecstatic that he's about to be rescued. The box the CSI's dig up contains a dead dog, and the scraping sound Nick was hearing was actually the box cracking, as he'd weakened it by shooting out the light.
Degrassi: Rick comes to school with a gun after being pranked by Jay, Spinner, and Alex, and the first person he meets is Paige. He slowly starts pulling out the gun...but stops when Paige says that the prank pulled on him was childish and unnecessary. He apologizes for hurting Terri, and then goes to his locker and puts the gun away, seemingly having calmed down. But while he's in the bathroom trying to clean up, Jay and Spinner stage a conversation framing Jimmy for the prank, and Rick snaps again, which ends with Jimmy getting shot and Rick getting killed by Sean in a struggle for the gun.
The series 4 finale, the Daleks are defeated yet again, and the Doctor finds himself surrounded by all of his companions and friends from the start of the new series including Rose, who he thought he would never see again. Everything is shiny and full of laughs for the next few minutes until half of them go home, Rose is left in a parallel dimension with the Doctor's clone, and Donna gets brain-wiped. The last few scenes show the Doctor cold and wet, staring ahead blankly in the TARDIS, completely alone again.
"Voyage of the Damned" has another powerful one. Astrid has driven herself off a precipice into the ship's reactor in order to off the Big Bad. As the Doctor returns to the other survivors, he is reminded that she was wearing a teleport bracelet with a system for rescuing passengers who run into trouble with it on. So he attempts to recall her... only to find that the system does not have enough power, and all that's left of her is constituent particles in her image, which he has to disperse. Hell of a Tear Jerker.
"The End of Time" is very mean about its Hope Spots. The Doctor has just saved the Earth and possibly the universe from the opening of the sealed time bubble and the return of Gallifrey, and he's still alive, despite thinking that The Master's return was what was supposed to have killed him. Then he hears a sound...four knocks, from Wilfred, on the inside of the flood chamber for the radiation, which The Doctor will activate if he lets Wilfred out, spilling the radiation on him...the entire rest of the special is a class one Tear Jerker through and through.
In "Fear Her", all the drawings have returned to life - except the Doctor. Then the other drawing comes to life, and it's an Oh, Crap! moment.
A particularly nasty and heart-wrenching example from "The Angels Take Manhattan"... Rory and Amy have jumped to their supposed deaths to create a temporal paradox to poison the Weeping Angels' food supply. The paradox puts them back where they started the adventure, ready to head out with The Doctor and River on a family voyage, until Rory is distracted by his own gravestone and is sent back in time by a weeping angel and Amy goes with him. And thanks to the paradox, the TARDIS can't go their time/place, so the Doctor and River will never see them again.
In "A Good Man Goes to War", Rory saves his and Amy's daughter from being kidnapped. Amy takes the baby and hides, while Rory, the Doctor, and the various aliens he recruited to help out form a line of defense to stop the last of the troops coming to get little Melody Pond. It turns out that the baby Amy was holding was just a "Flesh" replica, and the real baby had been kidnapped long ago. Yeah...
"Face the Raven"'s climax has several of these when it's revealed that Clara MUST die by quantum shade in the next few minutes thanks to her too-confident efforts to save Rigsy. She's still confident that the Doctor can fix this situation by finding another way to remove the chronolock...but he can't. He angrily threatens the mayor responsible for Rigsy's situation with a Roaring Rampage of Revenge against them and all the trap street residents if this isn't fixed immediately...but Clara talks him out of it (because he would cross the Moral Event Horizon if he did) and asks if there's anything that can be done in the first place — there isn't. Thus, Clara dies a painful death while the Doctor is delivered to what turns out to be a torture chamber and begins a Protagonist Journey to Villain that unfolds over the next two episodes, culminating in the events of...
"Hell Bent". The Doctor has become a Woobie, Destroyer of Worlds thanks to this Trauma Conga Line and via Batman Gambit manages to pull Clara out of time at the moment of her death, violating a fixed moment in time. He's overjoyed at the prospect of new adventures, brainstorming wacky new ideas like "cocktails with Moses" and inventing a flying submarine — but then he learns that she isn't fully returning to life...and the universe may well start falling apart soon, and it will be all his fault. He doesn't give up his quest just yet, but several conversations later he has a Heel Realization and accepts that his desire to staying with her forever is just a Tragic Dream that he must give up. Ultimately, Clara is able to continue "living" in her not-quite-dead state for as long as she likes, but this is technically not a hope spot as she is well aware that some day she will have to voluntarily return to the point of her death.
Dune: The 2000 miniseries has a beautifully dark and very justified one near the end of the final chapter, where Rabban, a brutal and savage oppresor of the people of Arrakis, find himself surrounded by the very people he had been oppresing. The Hope Spot kicks in when he sees Stilgar, the leader of the Fremen rebellion, bearing a rifle, and seeming to offer Rabban the hope of a quick clean death by gunshot... only for him to walk away, leaving him to the knives of a few hundred people who have no interest whatsoever in giving him a quick clean death.
Early Edition: Gary enters a hospital where some beat-up teen is being treated. Gary's newspaper tells him that the teen is slated to die. He tells the reception at the hospital about the teen who's about to die, but the reception insists that he's making a recovery. Suddenly, the teen indeed kicks the bucket, and the next scene is his funeral.
Firefly: The opening sequence to the first premiere. Mal charges out of the bunker to shoot down a flyer that was keeping away the reinforcements. He returns triumphant... and the reinforcements don't come anyway, as Command has surrendered the battle — and to make things worse, the reinforcements that do arrive proceed to rain fire upon Serenity Valley, killing everyone except Mal and Zoe.
FlashForward (2009): The episode after Al Gough screws destiny begins with a montage of the world and the main characters being optimistic after learning that the future can be changed.
Forbrydelsen: The cops finally work out who the murder victim was having an affair with, and the man in question immediately becomes the chief suspect. He takes prisoner the central character (a detective) using her gun, and tells her that he couldn't kill anyone, but then the detective's partner shows up. There's a standoff, which is ended when the partner shoots the suspect. All the evidence points to the suspect, and the victim's family, which has been tearing itself apart, finally comes back together in a tearful reunion. Trouble is, while the suspect was indeed having an affair with the victim, he really didn't kill her. The revelation of who did kill her will finally destroy the family.
Friends: In "The One Where The Stripper Cries," Joey appears on the game show Pyramid and, naturally, is terrible at it. His intial partner is a man who desperately needs the prize money. In the final round, Joey starts answering questions correctly, coming within one answer of winning. Unfortunately, he doesn't get it and they lose.
Being based on A Song of Ice and Fire, it is no surprise that the show does this a lot, too. An altered scene that adds an additional Hope Spot on top of those already in the books occurs in the middle of Season 2. Catelyn Stark manages to secure an alliance for her son Robb with Renly Baratheon, the sanest and most powerful claimant to the Iron Throne who also hates the Lannisters, in return for a token show of submission to Renly once the war is over. No sooner has Renly agreed to this before he is slain by a monstrous Living Shadow created by Melisandre, shattering his coalition which results in the Stormlands bannermen joining Stannis's cause, and the Tyrells eventually switch sides to the Lannisters.
In season 4 Tyrion has been accused of murdering Joffrey. The people still loyal to him are powerless to stop his conviction so Tyrion chooses Trial by Combat as a final Take That! to his father. It looks like Tyrion will be killed by The Mountain in the duel when Oberyn Martell offers to be Tyrion's champion. During the duel it soon becomes clear that The Mountain is losing and with his death Tyrion will be exonerated. Then right before delivering the final blow, Oberyn gets careless and The Mountain grabs him and crashes his skull with his bare hands. With his champion dead, Tyrion is pronounced guilty and sentenced to death.
Ramsay Snow deliberately creates these situations, understanding that providing the illusion of hope and then ripping it away at the last minute is far more psychologically damaging than providing no hope at all. This could be something as simple pouring water out in front of someone dehydrated, or as complex as acting like a friend and helping them escape, only to lead them right back to more torture, as he did with Theon. While still pretending to be Yara's agent and pretending he's escorting Theon to her, he listens to Theon reflect out loud on everything that brought him to this point. By the end, Theon's so overwhelmed with guilt and regret that he's fighting back tears. "Maybe it's not too late," Ramsay tells him. Theon says that it is, that he made the wrong choice, and he's burned everything down. "Not everything, my lord," says Ramsay, the implication being "You still have your sister, who sent me to rescue you. You're safe and free now." And then he leads Theon back into the torture chamber and reveals it was all a lie.
Viserys has a villainous one in "A Golden Crown." He threatens Drogo, demanding that the man helps him secure his claim to the throne. Drogo responds that he will give Viserys a golden crown "that men will tremble to behold", and Viserys is pleased until Drogo's bloodrider Qotho breaks his arm, making him drop the sword, and kicks him to the floor. Drogo melts his golden belt in a pot, while Viserys begs Daenerys to help him. Ser Jorah tells her to look away but she won't. She watches as Khal Drogo "crowns" Viserys by pouring molten gold over his head, causing Viserys to cry out in pure agony.
Ned Stark is set to resign to the Night's Watch, then Joffrey has him executed.
During a counterattack, Tyrion and his men rout the crew of a battering ram and raise a cheer, only to find more enemies charging them.
Locke stops threatening his captive with Eye Scream only to inflict another punishment.
Robb is set to receive reinforcements, is expecting his first child, has reconciled with his mother, and is about to be reunited with his little sister Arya when he is betrayed and butchered along with his wife, his mother, and nearly all his bannermen.
Sansa finally escapes, only to find herself in the hands of Littlefinger.
In the midst of battle, Ygritte hesitates to kill Jon Snow and The Big Damn Kiss seems imminent until an arrow sprouts from Ygritte's chest.
Yara Greyjoy's rescue party finds her brother, only for Theon to reject and bite her. Then Ramsay arrives with reinforcements and forces her to retreat.
Brienne of Tarth finds the object her quest and things appear to be going well, until the Hound notices the Lannister lion on her sword and instigates a very nasty deathmatch.
Grey's Anatomy: One episode had a main character finally wake up from being in a coma for a few months. Everyone is happy, though of them note that it's not unheard of for people in comas to wake up and have one final burst of energy ("the surge") before dying. Everyone denies it's the surge, saying he's going to be fine, because they don't want it to be true, though the patient knows he is going to die and plans accordingly. To the viewers, it's a Foregone Conclusion, since he died the episode before after slipping back into a coma and being taken off of life support after 30 days (as he requested).
The Handmaid's Tale: After Offred reveals what Handmaids really suffer to Mrs. Castillo, who listens with compassion, it seems that for a moment the deal with the Mexican government won't go through. However, Castillo then says that they have to, mentioning her home city where no babies have been born for half a decade.
In the episode "Six Months Earlier", Hiro travels back in time to rescue Charlie from Sylar. It turns out she would have died of a blood clot in her brain anyway. Then Hiro accidentally teleports to Japan and can't get back until she's already dead.
Subverted Trope when in season 4 Hiro goes back again, and this time convinces Sylar to remove the brain defect from Charlie then unsubverted when later that episode, after being cured, Charlie is kidnapped by the new big bad, Samuel, before Hiro and her can run off for their happy ending.
Kamen Rider Fourze: The climax of episode 31: the Kamen Rider Club instigates a Misfit Mobilization Moment among the Subaroboshi students, turning them against the Aries Zodiarts! Gentarou/Fourze has figured out the limits of Aries' power, allowing him to get the upper hand. Aries is about to be defeated, until Meteor arrives, reveals he made a contract with Aries, and then proceeds to kill Fourze.
The Hitchhiker's Guide to the Galaxy: Parodied in the scene where Ford and Arthur are about to be blown out the Vogon airlock into deep space, and Ford suddenly says: "Wait a minute! What's this switch!", complete with a hopeful swell of music. But it turns out he's just being a jerk, there's no switch, and they do indeed get blown into space.
We spend most of the episode "Greatest Hits" expecting Charlie to drown in a flooded underwater station at the end. When he gets down to the station, it's not so much flooded, and Charlie doesn't die. Then in the next episode, Mikhail does flood the station, and Charlie dies in a poignant Heroic Sacrifice.
There's also the bizarre double case in which Locke, after causing Boone's death, goes to the hatch and begins banging on the door and screaming. This causes Desmond, who was in the hatch, to abandon his suicide attempt because he thought Locke was his new button pressing partner. Desmond shines a light up through the hatch door just as Locke is screaming at the island to give him a sign. Of course, Locke turns out to not be Desmond's new partner and, when he finally gets into the hatch, the computer winds up getting shot, which causes Desmond to both panic and go into a deeper depression than before. Locke goes on to destroy the computer that gave him so much hope and purpose after he loses faith it. Later on, Locke is killed, taking away his "special" status and making his moment of hope meaningless.
Masters of Horror: In the episode "Sounds Like". Larry hopes that with his sense of hearing becoming superhuman he may finally be able to "hear" his son's ghost. Near the end he thinks he's finally heard it, but it just turns out be a tree branch scraping against his son's bedroom window.
"Master Vile and the Metallic Armor part 2". Tommy and Kat have nabbed the Zeo Crystal and recovered the Falconzord. They blast Globbor, then combine with the Ninja Megazord to pound him. They seem to have won... then Globbor gets up and turns out to be just fine. And worse still, Master Vile reveals that Ninjor is absorbing the damage. Globbor defeats both the Ninja and Shogun Megazords, which are then teleported to a distant planet with the Zeo Crystal now linked to Master Vile. Globbor also drains the Rangers' energy, weakening them severely.
An earlier example combines this with Like You Would Really Do It: Rita has created a candle that will destroy the Green Ranger's powers when it burns down all the way. Jason fights his way to the candle, but must leave before he can extinguish it because the rest of the team is about to be defeated in battle. End result: Tommy loses the Green Ranger powers and the team loses a valuable ally until the White Ranger powers are created.
NCIS: Kate's death. She's shot in the chest, but was wearing a bulletproof vest. She gets up, and is killed mid-sentence. Sentence being? "I never thought I'd live-" She's shot in the head, by the way. No way she's surviving that.
NCIS: Los Angeles: This LA counterpart to NCIS has one with Dom. Kidnapped by terrorists and missing several months, the team discovers he's in LA and launch a rescue mission. Sam finds him on the roof (Dom admitting he always knew they'd find him), only for Dom to get gunned down moments later.
New Tricks: Rare example of a villain getting this from the pilot episode; a vicious, psychotic gangster has been released from jail following an appeal of his conviction for the murder of a barmaid two decades beforehand. He and his friends are celebrating in a posh restaurant when the main characters enter the restaurant to officially and publicly announce that DNA evidence has cleared him of the murder he was convicted for. The entire room begins to celebrate... moments before more police officers flood in to arrest him for another murder two decades ago that same DNA evidence can now conclusively prove he did do. Oh, and turned out his wife murdered the barmaid under the mistaken impression she was having an affair with her husband.
Beecher tries to go on a Roaring Rampage of Revenge following seeing Keller, the guy he fell in love with, working with Schillinger his enemy but in a tragic Hope Spot fails and ultimately gets his arms and legs broken by Schillinger and Keller.
A worse one comes in the end of season 4. Beecher is paroled thanks to an attorney he fell in love with, says goodbye to Oz, gets finally out...and wakes up. It was All Just a Dream, and in reality, Beecher's parole is refused, and he is forced to stay in jail. The show's future was uncertain at the time; in the event of the show's cancellation, the scene would not have turned out to be a dream.
It gets even worse than that for Beecher. In season 6 he actually does get paroled, but after about six weeks, Keller tricks him into violating his parole and having to serve the remainder of his sentence.
Revolution: In episode 11, Danny dies taking out Monroe's only power amplifier with him. By doing this, he effectively brought Monroe back to square one. Randall shows up on Monroe's doorstep not too long after, and not only offers him more power amplifiers but soldiers, weapons, and a full team of scientists.
The series two finale is just one Hope Spot after another, culminating in Robin and the outlaws tied up to die of exposure in the desert. Marian arrives on the horizon to the joy of everyone, only for her to be accompanied by the Sheriff and tied up with them. A second miracle arrives in the form of Carter, who frees everyone, only for the Sheriff to kill Carter and Guy to murder Marian at the climax of the episode.
The two-part Grand Finale of same show can be summed up as "Every time things seem to be going right, somebody dies". The Sheriff's army make their presence known by dumping Allan a Dale's body at the castle gates just when it seems like the battle's over; when it seems like the blocked escape tunnel might be cleared allowing everyone to escape, Gisborne gets killed and Robin is lethally poisoned. The other outlaws don't find out about this last part until the castle and the Sheriff's entire army has been killed.
Samurai Sentai Shinkenger has one in one of the last episodes: the Shinkengers have an all out battle against Dokoku to buy Kaoru the time she needs to use the sealing character that will lock the Big Bad away forever. Just as the team gets beaten to within an inch of their lives, she finishes and the seal hits Dokoku full force...only for him to walk out of the explosion with only minor damage, as he found a way to negate the effects of the character. The episode ends with Kaoru too injured to continue fighting and the others desperately searching for a solution.
The Sarah Jane Adventures: In "The Temptation of Sarah Jane Smith" we see present day London in ruins and Sarah Jane and Luke stuck in the past with no idea how to defeat the Trickster without...bad things happening to Sarah Jane's parents. Cut to the image of a Blue Police Box as the Doctor's music plays. It turns out to be just a policeman.
The Star Trek: Deep Space Nine episode, "The Sound of Her Voice" is one big Hope Spot. The crew of the Defiant receives a distress signal from a shipwrecked Starfleet officer, who is slowly suffocating on a not-quite-habitable planet. As the Defiant rushes to her aid, the senior staff take turns talking to her over the radio. The rescue party seems poised to reach her in the nick of time... only to discover that an ill-explained time displacement was in effect, and she's actually been dead for years.
The first half of the finale of Season Two has Sam and Dean separated and when they finally meet each other, Sam's rival stabs him in the back and he dies in his brother's arms.
"Mystery Spot" also had one of these. It's a Wednesday, Sam thinks they've beaten the trickster but Dean gets shot by a mugger, is dead before Sam even gets there and Sam can't even wake up this time.
As Sam pretty much says in "Devil's Trap", they're still alive (although Dean just barely), they've still got the Colt and we would love to believe him if it weren't for the fact that, in a second, a giant truck will smash into their car on purpose, leaving all three of them bloody and unconscious. What a fun way to end the season!
Every season finale. "No Rest for the Wicked" has them just about to murder Lilith— the entire episode is one giant Hope Spot— and Lilith turns out to be possessing Ruby, and Dean goes to Hell anyways. And then "Lucifer Rising": Dean gets to Sam with some help from Castiel, Sam hears him calling out and puppyfaces "Dean?", and then Lilith hits his Berserk Button and he kills her, destroying the final seal and releasing Lucifer from his prison.
Veronica Mars: It has one. Logan's mother dies, and he is convinced that she is still alive. He asks Veronica to find her for him, and she tries to do so ( encouraged by the knowledge that the woman who supposedly witnessed the suicide was a liar) until Weevil finds a freshman with a tape that shows, amongst other pointless things, something dropping off the bridge at around the time that his mother jumped. Logan then gives up until Veronica gets a hit on one of his mother's credit cards- someone used one. They go to where it was used and find a woman there. Logan tentatively asks 'Mom?' only to have the woman turn around to be revealed as... his sister, Trina. Makes you want to cry. Supposedly her death was purposefully left ambiguous in case the writers wanted to bring her back, so does that count as only a half-futile hope spot?
The Young Ones: The final episode contains a two-fer; the boys have escaped their confining, limited lifestyles and have their whole lives ahead of them, full of freedom and hope... only for them to accidentally drive through a billboard of Cliff Richard and over a massive cliff. Then, at the very bottom of the cliff, from within the battered, smashed-up bus we hear them say "Phew! That was close!"... and the bus explodes in a massive fireball from which nothing could survive.
Air Crash Investigation: Air Canada Flight 797 managed to safely land after a severe on-board fire, and it seemed that the passengers would all make it off the plane... until the plane's doors were opened and a flashover occurred, which incinerated the interior and killed 23 people
In arguably the Trope Codifier for the cliffhanger season finale, "Best of Both Worlds Part 1." After the Borg abduct and assimilate Picard, Riker gives the order to fire the superweapon improvised from the main deflector dish. The music swells and Enterprise opens fire. Only for Part 2 to reveal the Borg have already adapted for that weakness, because they learned of it from Picard. The Borg No-Sell the attack, Enterprise is temporarily disabled, and the cube heads to Wolf 359.
Two seasons earlier, Picard had one in "Conspiracy" when he was trying to escape from a room of possessed admirals and ran into Riker, only to discover that he was possessed, too. Subverted, though, when Riker revealed that he was only acting possessed so he could get close enough to shoot the admirals.