2muchvid's list of Single Player Half Life mods:
Moddb.com
A long list with mods and maps
Pcosmos.ca
Mods and maps: Andrej.mernik.eu
Half Life mods: Moddb.com
Half-Life maps: Moddb.com
GmanMaster's list of Dead Half Life mods Moddb.com
Blue Shift:
Moddb.com
Opposing Force:
Moddb.com
My favourite Mods:
Heart of Evil, HL Invasion, they hunger, the trap, Visitors, Radiation Alert Episode one, Infestation, Residual Point, Timeline 1 – 3, ETC 1 & 2, USS Darkstar, Edge of Darkness, Escape 1 & 2 (dmcia.de), Brave Brain, Hard 2, Cry of Fear, Cleaners Adventures, Hidden Evil, The long night, Instinct, Operation Nova, Hard 2, Escape from the Darkness, Operation Firesweep, Freemans Revenge
my favs from the old ones (ones I played awhile ago) would be Smart Decoy, Deliverance, USSdarkstar, ETC, and Lifes End. Newer ones: E7 Blackstar, Focal Point, Challenger Deep 2, Residual Point, The Evasion, Todesangst 2: Der Echte Feind, Black Mesa Sideline, Dead Sector, DAVsub, Minimicus, Cleaners Adventures, Nuclear Winter, Operation Black Thunder, Intolerable Threat and Timeline series. With perhaps The Trap and Point of View coming in close 2nd and above average mods would be a long list but those that are noteworthy: Surface Run, Infinite Rift, Poke646, Timefall, Invasion, Paranoia, Destination Black Mesa, Brave Brain, ETF, Xeno project, Zubben, Black Guard, Black Ops and Case Closed (all great but come just shy of being special, have somekind of noticeable flaws). I liked They Hunger but I personally wouldn't put it in my top favorites list. Recent ones I played with missed potential would be Prototype 98 and Lostworld.