Honorable Mention: Homeworld - The Bridge of Sighs
While Homeworld is, ultimately, my favorite game in the franchise, I can admit that it doesn't really have any stand-out story battles like Homeworld: Cataclysm. Where itarguably makes up for it is with battles that are much more interesting in terms of mechanics and the sometimes unintentional opportunities they present. The Bridge of Sighs is perhaps the finest example of this, pitting you against an overwhelmingly massive fleet that's most easily defeated not with grand tactics or a force of arms, but rather with Salvage Corvettes.
The entire point of the level is destroying a field generator that's preventing you from jumping to your race's titular homeworld. Defending the generator is a massive globe of Ion Frigates that will attack and overwhelm you the instant your fleet move near them. Even with a strong force, trying to fight these buggers can be suicide. There are just too many of them and if you try to push straight in you can quickly find yourself being attacked by swarms of ships that come at you from every direction.
The best way to defeat them is to build a sizable squadron of Salvage Corvettes, draw the enemy frigates out with your fighters, and then capture them one after another after another. It's a bit cheap and, honestly, the massive fleet you wind up with as a result pretty much breaks the difficulty for the rest of the game. That said, it's still a lot of fun and provides one of the more memorable (albeit for the wrong reasons) moments of the game.