The end of Shadow of the Colossus, when you're getting sucked into the fountain, and you realise that you've roamed he land, far and wide, fighting things hundreds of times your size, sacrificed your last shreds of humanity, and seen the death of the only living thing that was there to help you, and it has all come to naught. You've lost. Game over. You gave everything you had and now you have nothing left.
Yes, the girl gets brought back to life and you're turned into a baby, but you don't know that. The player doesn't know until after it's happened, and Wander doesn't get informed ever. I thought it was a rather interesting moment in gaming. It's not often you get sad endings outside of the survival horror genre. Not to mention just the saddest thing I've ever seen. Big mighty super-colossus reduced to a stumbling baby...damn I love that game.