Brutal Legend easily has the best sound track. The game is metal in video game form.
To be fair, that, much like rhythm games, is how it was designed to be: music in game form.
My favorite soundtrack out of any game...that's a toss up, Bioshock and Silent Hill 2 both have incredibly atmospheric moods and an atmosphere is nothing without its soundtrack.
I would have to say Gun, though. I received one of those speaker chairs a few years back for Christmas. You sit in one and it's surround sound in a tiny space. Gun's soundtrack blew me away and even without the chair (as it has been long broken) Gun still brings a smile to my face.
A worthy mention is Metal Gear Solid 3; the intro song of the game perfectly captures the mood.