Personally some of my favorites would be Final Fantasy 7, and Castlevania: Lament of Innocence. Those two probably did the best job overall of really setting the mood with the music. The first Halo had a pretty good soundtrack. Xenosaga 3 had some EXCELLENT music at times, like when the Miltian Conflict breaks out and you're fighting your way through the city.
Persona 4 was pretty good, and even Persona 3 wasn't too bad, even if the whole rap thing was a little odd. God of War's (the first one) music was rather fitting as well. I also admit that I love cranking the stereo system for the Zanrkand Blitzball cinematic in Final Fantasy X, and the game had some good music on the whole, but it also did a really good job of using the absence of music to help set the mood.
I was a bit disappointed with Final Fantasy XIII's music. It wasn't bad, it just rarely fit with the mood of the game. Like when Vanille and Sassz were making their way through a forested area, the music was kind of a light jazz, which just didn't fit at all. It did redeem itself on Pulse when you reach the destroyed settlement. That music was appropriately haunting.
Final Fantsy VIII had some pretty good music, especially when you're fighting the Galbadia Garden.
Prince of Persia: The Sands of Time and Warrior Within also had some good music that helped set the mood appropriately.
I recently played Persona 4 and fell in love with the battle music in the game--the best track of all times--it makes you want to go on and on.
What is your all time favorite game track?