Graphics & sound overhaul for Morrowind.
Too many to list for stalker, but in particular I do like Misery 2 for Call of Pripyat / Lost Alpha (with or without extra mods).
Surprisingly, although I'm not generally a fan Crysis, I have obviously enjoyed MaLDoHD / Blackfire's mod for Crysis 2 quite a lot. Must have reinstalled that several times now.
Single player mods for Quake / Quake 2 have been great fun to play, too.