There are, in North American sports, a number of contenders for the biggest collapse of all time. Greg Norman at the 1986 Masters, the 2011 Red Sox (or Braves), the 1951 Brooklyn Dodgers, the Yankees in the 2004 ALCS, the Houston Oilers in the 1993 Wild Card round, the Detroit Red Wings in the 1942 Stanley Cup finals, Lindsay Jacobellis in the 2006 Winter Olympics. There are options.
In British sports, though, while you'll find some people arguing for Jean Van de Velde at the 1999 British Open, there's really only one choice: Devon Loch.
What the Kentucky Derby is to the United States, the Grand National is to the United Kingdom. If you don't watch horse racing the entire rest of the year, you at least tune in for the Grand National. And every year, you're going to see an oftentimes unsettling show. The Grand National, if you've never heard of it, is a steeplechase, with horses jumping a series of 30 obstacles along the way. The obstacles are not, well, the only obstacle, as horses regularly miss these jumps, or refuse to attempt them, and often shed their jockeys trying. This leads to 'loose horses' running the remainder of the course themselves, getting in the way of horses still in the race (a horse must cross the finish line with its jockey). And, of course, a horse might just go down and stay down, giving the others something extra to avoid. The Aintree course near Liverpool, where the race is held, commonly sees horse fatalities due to the course's difficulty, and the Grand National itself has seen 11 horses die since 2000, which despite occasional efforts to make the course safer is about the normal rate.
A fairly typical race, the 1997 edition (which saw two fatalities), can be seen here. (More recent races have embedding disabled, but here's this year's.
So, that established, on to Devon Loch. Owned by Queen Elizabeth I, Devon Loch raced in the 1956 Grand National. Those of you in the UK know this clip well. But I'm not writing this from the UK. (Note: that clip has a bit of a time disparity between action and narration.)
Armorial III led most of the race until going down at the 26th jump, allowing Devon Loch to take the lead. Devon Loch held off E.S.B. and Gental Moya until about 40 yards from the line, and then...
For whatever reason, Devon Loch belly-flopped onto the dirt, allowing E.S.B. to take the win. The horse was fine, but failed to finish. The going theories are that Devon Loch either got confused by a shadow being thrown by an adjacent jump (that's only contended with on Lap 1) or by the roar of the crowd that was anticipating a win by the Queen's horse. (The Queen's opinion: "Oh, that's racing.") Either way, even for a horse that's pretty dumb.
Of course, the horse has the excuse of being a horse. When a human does it...