Recently I'd say Darksiders was a pretty terrible game overall but had a delightful "...oh SHIT!!" ending to it.
Castlevania SotN's faux ending is pretty memorable, but overall... Jet Set Willy. I seem to go on about that game a lot, but it's epic. Almost impossibly difficult to complete (literally if you've got a version with bugs), it took me about a decade before I finally got every last item in the game and then the hero, Willy runs to the nearest toilet and falls down it, head first. Simple but effective.
Sounds pretty epic. I've just remembered the ending to one of the Monkey Island games where it just tells you to turn your computer off and go to bed. Hilarious.
Legend of Zelda: Ocarina of Time StarFox 64 (True ending when you meet your dad and fly the hell outta Andross' lair)
For an iOS game, Karoshis ending was perfect. It should hold a spot in the hearts of anyone who hates their job
Metal Gear Solid 2: Sons of Liberty. From the point that you enter Arsenal Gear, your mind will be blown.
I remember wanting to see them all, but can't actually remember what any of them were. They obviously did the trick though since I went through the game with every character because of it.
Final Fantasy: Crisis Core. Heart wrenching stuff Also, not intending to sound like a fanboy, but I loved the Halo Reach and Halo 3 endings Portal ending was also quite satisfying, as was HL2
