I will Second Max Payne 2 & Fahrenheit, two of my all time favorites.
I would also like to chime in for Morrowind, not because it looks amazing, but because its understated, yet effective, it conveys what the game is about without an action scene on the front cover.
You look at that Box art and you just know your in for an amazing and epic adventure. Like the front of an old book that's about to transport you to another world.