idbeholdME: One very well done Last Stand RTS mission was the final one in SC2 Wings of Liberty. You really feel like you are desparately holding out against overwhelming odds until the artifact charges. There is no escape, no alernative, either success or death.
The final Undead mission in WC3, defending Kel'Thuzad is similar. The last attack wave that comes like 90 seconds before the end is extremely overwhelming and bound to destroy most of what you have.
RTS in general are a good source for this kind of thing. Too bad the genre id pretty much dead. I hope Age of Empires IV will bring about something of a classic RTS revival.
Warhammer gladius, while turn based knows also similar endings for the different races if you decide to follow the story campaigns, nothing beats multiple necron pyramids emerging from the deep surrounding your base with 2 turn left on the clock and the mission to keep your base safe for those 2 turns,
the end story is also very neat which tells a tale of how the gates are smashed in and the necron overseer marches in with the end times device ( just a name ) still beeping behind you, not sure if you are hit by some weapon on that point but in the last second for your demise as commander the machine stops beeping and turns green so to speak, leaving you as commander of the present elite forces of the emperor with the only action left to activate the device