bioshock is a great single player game.... in fact, as far as story and atmosphere, one of the best i have ever played. Single player only, but this one will keep you glued, wanting to find out what is next.
crysis is amazing, if your computer can handle it, otherwise its just ok. the story is just ehh, and there is nothing revolutionary about the gameplay, but the graphics are far and away the most photorealistic i have ever seen in a game... but as such, the game is a beast and requires a monster computer to run it in all its glory.
call of duty 4 is basically like playing a movie, extremely well done and relatively low system requirements, i think. I havent tried the multiplayer, but single player has a good story, and the whole game is very cinematic, not the best graphics i have ever seen, but still good.
unreal tournament 3... its all deathmatch and basically online type play, ie, no real story in single player mode. very very fast paced.
those are the big recent ones