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