In recent memory, Wolfenstein II had an amazing ending. Definitely worth the entire experience.
I also loved the ending to the original Tomb Raider II game, where you fight some bad guys in Lara's mansion and nearly get to see her naked before she asks "don't you think you've seen enough?" and shoots the camera with a shotgun. Ahhh, good times.