Heh, I'm surprised nobody has replied with the Kanye meme to this thread.
For me, the one I have spent most time playing is Jedi academy, first with ja+ mod, then with MBII (which is a great mod).
Other mentions:
Half life story is very good.
KotOR 1. 2 is ok, but the story in 1 is fantastic.