Shadow of the Colossus is a pretty famous game with a sad ending.
It's not a total loss, but from the very beginning of the game you can hear the train coming and at the end it's finally time to stand on the tracks.
There are plenty of games with sad endings. There just aren't many famous games with sad endings, by which I mean big, AAA titles in the vein of MGS, Modern Warfare, or Final Fantasy. If you want to find a few sad endings, you have to step away from the center stage; sad endings just piss most people off. It turns out people like to get endings where everything they worked for doesn't come crashing down and everyone they liked dies. Go figure.
The most gut-wrenching ending I can think of at the moment was the end of Fatal Frame II. A worse one would have been ICO, but they turn it around at the last moment. ICO had left me all but despondent through the credits, though.
There are even games with 'happy' endings that seem awful only if you think about them too long. At the end of Ace Combat 5, you've stopped the war and blown up the SOLG and the Belkan aggressors, which is good... But millions of people have been killed in a pointless proxy war, and both countries are still infiltrated head-to-toe by Belkan traitors who have nothing left to lose and will probably seek to sabotage every industry and government body and assassinate every political figure and war hero they can, including Nikanor, Harling, Anderson, and you and your squadron. The war won't be 'over' for another decade at least, and the climate of fear and paranoia it will create will last for decades.
How's that for a sad ending?