I've given the same answers in a topic about "best endings," or something like that, but here we go:
Planescape: Torment - probably the only game ending that ever moved me.
Vampire: the Masquerade - Bloodlines - for the whole game, you're looking for a thing called the Ankaran Sarcophagus, while people keep telling you not to open it [http://lparchive.org/Vampire-The-Masquerade-Bloodlines/Update%2071/vamp2204008.JPG]. Well, there are a few options in the end but the most appealing, of course, is to look inside the damned thing. I will say no more.
Legacy of Kain: Soul Reaver 2 - an obvious sequel tease, but a well executed one. Although I'd played Defiance first, so I already knew how it would play out (and it made much more sense now).
Portal - that one's pretty obvious, isn't it?