For me, Leeds United losing to Sunderland in the F.A. Cup Final.
Although the OP asks about heartbreaking for fans, I will add a couple of examples that were heartbreaking for the player and you had to feel awful for them just watching:
Jean Van de Velde blowing a 3-stroke lead on the 18th in the Open at Carnoustie.
Jana Novotna suffering a meltdown against Steffi Graf when the Wimbledone Ladies title appeared to be hers.
Wimbledon Ladies’. I did preview. Really.
**That was '93, which I remember because it is irreversibly etched in my damaged psyche. I was convinced if we forced a 7th game, we would win it. A lead in the ninth, and we blew it. I still blame Fregosi, not Mitch, for taking out Roger Mason, who had retired 7 batters on 28 pitches. West and Anderson struggled, turning over the order even though they didn’t give up any runs.
Anyway, you’re a good person for sympathizing with Mitch. As pissed as my family was, we sent him a letter after we heard of the death threats. You’ll be happy to hear he has been warmly received at the Vet. Philadelphia fans have a well-deserved reputation for being tough, but we appreciate those who put themselves on the line and try their hardest.
I’m older than you, and as bad as this was, losing the 1980 Super Bowl to the Raiders was worse. 
1988, Game 1 of the World Series. The A’s take a one run lead into the 9th with one of the best relievers of the day, Dennis Eckersley, on the mound.
After Mike Davis walks, Kirk Gibson comes out to pinch hit for Alejandro Pena. The guy is so wracked with injuries that he is limping as he comes up to the plate. Whack. Two-run homer, the Dodgers win Game 1, the A’s fold, and we are forever treated that shot of Gibson giving the arm-pump halfway between 1st and 2nd base.
It was his only at-bat of the entire Series.
As a baseball fan, it was unbelievably dramatic. As an A’s fan, I still get that same old feeling everytime I see the g*ddamn highlight.
Pistons-Celtics.
“Bird stole the ball! Bird stole the ball!”
Imagine my joy when I moved to New England and this was part of the intro to every freaking Celtics game.
England losing to Argentina on penalties, World Cup 1998.
Oldham Athletic 1-1 Manchester United, FA Cup Semi Final. Not a defeat, but a last minute equaliser that resulted in a crushing replay defeat.
Mary Decker-Slaney falling down after colliding with Zola Budd. Heartbreaking photograph here.
Oooh, oooh, ooooh!
Another one. If you’ll forgive me, it’s another F1 example.
Damon Hill won the 1996 world championship, but was ditched by the Williams team nonetheless. In 1997, he drove for the modest Arrows team. Now, the Williams was still the car to beat, and the Arrows sporting the #1 was never near the podium spots all year long.
Except for that one hot afternoon in Budapest. The godawful Hungaroring track suited the underpowered Arrows (which had a decent chassis), and Damon Hill saw himself leading in the last parts of the race, against all possible odds! Jaqcues Villeneuve, world champion to be that year, was running in second place, but well behind.
Hill was going to make it - he was going to give Arrows their first F1 victory since the team started racing in the late 70’s. And, he would get his revenge on Frank Williams, who dropped his world champion like a bad habit. It was a fantastic victory in the making - just a few more laps!
It wasn’t to be. In the last lap, the Arrows suddenly started losing speed - a gearbox problem? Lack of fuel? It turned out to be a hydraulics problem, affecting the throttle. Villeneuve was closing in like mad on his former teammate.
Hill did what he could, and fought the Arrows to the finish line. Unfortunately, Villeneuve made it past him 9 seconds before Hill reached said line. Had to overtake him through the fucking grass, too, as Hill wasn’t going to go down without a fight. (Small video clip of Villeneuve overtaking Hill, MOV format, right-click and “save as”)
I’ve never seen anyone SO disappointed with second place, especially considering the fact that he got on the podium in a bloody Arrows. But while I’m no Damon Hill fan by any stretch of the imagination, dammit, he EARNED that race, and lady fortuna took it away from him. Heartbreaking indeed.
Ah, Red Right 88. That was the beginning of my long, slow journey into not being a sports fan, which was completed when the last moving van left for Baltimore.
For me, in recent memory, the loss to Cal by USC. I was sitting in a drizzly cold rain outside in Indianapolis, fighting a generator to maintain power, only to see us lose in overtime. We would be undefeated now if not for that game.
Of course, being ranked #2 right now, we have a real good shot at the national title, so the loss to Cal might be a moot point and something only they will remember.
Alain Prost losing the World title by a half point was kinda tough also.
She told me she loved me like a brother. She was from Arkansas, hence the Joy!
Damn. You guys are going to hate me for posting once again. 
I’ve got another one, and this one probably trumps them all. Well, in sheer magnitude, anyway.
I am talking about the 1989 Tour de France. Two time champion Laurent Fignon was leading comfortably by 50 seconds - Greg Lemond was second. Only one stage separated Fignon from victory: contrary to usual practice, a “contre-le-montre” would be the final decider.
Fignon, a well above average time trial rider, put in a good time.
And then, the waiting started. The wait for Lemond, en route towards the Champs Elysees. And even though Lemond seemed to be doing faster than Fignon, the latter wasn’t getting nervous until the former suddenly appeared at the horizon… a little too soon for comfort.
But surely, 50 seconds would be enough?
'T weren’t to be for monsieur Fignon. Lemond dashed towards the finish in an unbelievable pace, and crossed the line 58 seconds faster than Fignon had done.
Lemond won the 1989 TdF, in the deciding time trial, from a 50 second setback, to the narrowest victory in recent history.
Three weeks of cycling, and a mere 8 seconds divided the two fastest men. Fignon walked off furiously, almost disgusted. Journalists trying to get a mike in were met with shoves or, worse, punches.
That scene, of Fignon walking away from the cameras after Lemond crossed the line just 8 seconds “too soon” - that’s the textbook example of defeat.
September 11, 1999
A week after losing an overtime game at Boston College on a missed extra point, my Baylor Bears were leading UNLV 24-21. We’d just gotten a first down on the UNLV 8 yard line with less than 30 seconds to play. Instead of taking a knee to run out the clock, our new coach decides to “establish an attitude” by running it in for another score.
Instead, the running back fumbles and UNLV runs it back 99 yards for the win as time expires.
We have a different coach now.
March 29, 1984 - Under cover of darkness, Mayflower moving vans pulled out of the Baltimore Colts complex in Owings Mills, Maryland bound for Indianapolis.
World Cup 2002 quarterfinal. USA loses to Germany after putting on one of the most surprising performances of the tournament. I’m getting worked up just thinking about it.
1976 NBA playoffs. The Phoenix Suns finish the season on a LOSING record They (40 wins and 42) then upset the defending NBA champion Golden State Warriors in the playoffs, ending up against the Boston Celtics in the NBA Finals (Celtics won in six games). The pivotal Game 3 was a triple-overtime classic heartbreaker. My wife kept calling from the bedroom “Isn’t it over yet?”
“No, honey, they just went into another overtime!”
One site describes it this way at the end of the second overtime: “However, with 1 second left it appeared as if the Suns had burnt out as they trailed by 2 points, as it would have taken a miracle to extend the game to a third overtime. However, a miracle is just what happened as Garfield Heard caught the inbound pass and turnaround hitting a buzzer beater to force a 3rd overtime. However, with several key players fouling out the Suns would fall in the 3rd OT 128-126.”
Here’s one with a little different tack.
1988, Seoul. In the 100m, Ben Johnson blows Carl Lewis out of the water. It was beautiful. The entire nation was euphoric.
And then, of course, he turned out to have been cheating, the bastard. Gah. Not enough to just lose in a heartbreaking fashion, no, he had to first make it look like he’d won (and how!), and then reveal that it was actually a loss the next day.
What a race!! If I recall, 50 seconds in an individual time trial was considered nigh to impossible to overcome, especially considering the short distance of the final day.
Apologies to Airman Doors, who probably would have a much more storngly worded rant for this event.
The Super Bowl. Sun Devil Stadium. January 1996.
The Steelers had momentum, and were driving to take the lead from the Cowboys, who seemed to be losing steam. Then, Neil O’Donnell decides to make a defensive back who would leave the team before the next season the MVP of the game, by tossing not one but two passes his way to kill drives and give a score to the Cowboys.
I wish the Steelers could get that close again. It’s rough having your hopes dashed in November.
I concur with Coldfire Ol’G. Lemond’s performance falls in the category “Just because it happened doesn’t mean it’s possible.”
Greg Norman, '96 Masters. All he had to do was shoot an average score on the last day and he waltzes to the Green Jacket. Instead, he completely folds, and will never win a jacket.