These are some mods I've enjoyed playing:
Mod - Game:
They Hunger/Poke 646 - Half-Life 1
Matrixed Reality/LightSaber 4 - Max Payne
Close to Truth Lobby Edition/Free to Kill - Max Payne 2
Simpsons Mod/Twilight Warrior - Doom 1 & 2
Doom Reborn/Shamblers Castle - Doom 3
OldSkool Mutator and Mods - UT99
Edit:
You guys gotta try this great mod for Max Payne 2:
Close to Truth Lobby Edition
http://www.megaupload.com/?d=R1CSVN7T