According to fun or time spent:
Half-Life 2
Team Fortress 2
Warcraft 3
Diablo 2
May Payne 2
World of Warcraft 
Portal
Fallout 2, one of the few singleplayer games I played through multiple times, an amazing feat  
 
Dwarf Fortress, a free game developed by one person that allows you to control of group of dwarves, building a fort. Or deathtraps. With ASCII graphics.  
 
Honorable mentions:
World of Goo
KotoR 2 but it was just so buggy and the end is complete crap because half of it was cut out
And because someone mentioned the Nintendo 64  

 :
Goldeneye multiplayer
The Legend of Zelda: Ocarina of Time
Perfect Dark multiplayer
Bonus Section: My SNES and SEGA Genesis favorites, because many games mentioned here are not really that "recent"  

 :
Secret of Evermore / Lufia / Terranigma
Sonic 3 & Knuckles