Hmm, there are many games with great soundtracks, but surprisingly not so much of them use the music to full extent.
For example, while Halo always had a fantastic Soundtrack, i never felt it is used in a good way outside of the cutscenes.
That said, the Shadow of the Colossus Soundtrack comes into mind. It feels like during the game, the music provides a great buildup for the confrontations, and when they finally happen, you fight with a really epic score in the background.
Hitman: Blood Money had an equal quality.
Other than that, i second SSX, because it shows how much you can do even with licensed Music. Also, thanks to the Music, playing SSX1's "Snowdream" for the first time ranks amount my favorite gaming moments ever.
I'm so glad someone created this thread, as I'm a huge fan of computer game soundtracks.
Here's a small list in no particulary order.
The ones highlighted in bold are ones for best of all time:
Guilty Gear X Heavy Tracks Tenchu I & III
Panzer Dragoon, Orta and Saga
Parodius: Fantastic Journey Klonoa: Door to Phantomile
Jet Set Radio Future Soul Calibur Myst III: Exile - ALL TIME FAV
Keio Flying Squadron
Final Fantasy VII, VIII, XI & XII
Wipeout (Sega Saturn version)
NiGHTS into Dreams Phantasy Star Online Songs of Ragol and Episode II Sonic CD, Sonic 3D & Sonic 3
Disgaea: Hour of Darkness
Stella Deus Vagrant Story Shadow of the Colossus
Street Fighter EX + Alpha, SF Alpha 3 & SF4: Third Strike Silent Hill 3 & Origins
Monkey Ball & Monkey Ball Banana Blitz (co-incidently if anyone knows where to get hold of the soundtrack, please let me know)
Grand Theft Auto
Curse of Monkey Island
Dead or Alive 2 Trax
Daytona USA Ridge Racer Type 4
Super Smash Bros. Melee Medal of Honor: Frontline
Spyro the Dragon
Resident Evil (original)
Capcom VS SNK 1 & 2
Dune 2000 Broken Sword: Knights Templar
All of the Zelda games, but notably Ocarina of Time, Majora's Mask and Wind Waker (haven't played Twillight Princess, but I'm sure the score's just as worthy). Certainly Shadow of the Colossus, for the epic moments (and for causing me to try and get the final stab in as it comes to a crescendo). I agree with Parappa the Rapper 2, and have to add the sounds of Gitaroo Man.
But my personal favourite? Killer7. Anybody that's heard 'Rave On' when you reach the Vinculum Gate can vouch for me on this one.
We used to call the battle music in Shadow of the Colossus "Beard Music" in my circle of friends, after the fight with the tall one with the large beard being their first introduction to that awesome game. It would then be sung during an encounter in a DnD campaign to aid morale and rolling.
I have special memories of FFVII, Ocarina of Time and thought the Halo series stuff was a great addition to the feel of the game.
The Metal Gear Solid series has been fantastic (especially 3), and the Legacy of Kain series created atmosphere for the game play.
I'm glad some developers put so much effort into making it work.
I'm an unapologetic raving fan of the Halo series soundtracks, having bought all four soundtrack albums and playing them regularly. I also love how the game's music engine changes the mix and sequence to fit what's happening on the screen, as if my gameplay is being "scored" the same way a movie would be... and how Halo 3's engine does the Dolby 5.1 mix in real-time so everything fits together perfectly.
Psychonauts got it right as well; the right music at the right times, and silences when they were needed too.
Mass Effect's soundtrack was good but a bit repetitive... it needed more silences to break up the sense of "wallpaper" that crops up during all that replay.
Bioshock used its soundtrack to great effect, both the licensed stuff and the originals.
Going way back in time, I loved how XCOM's soundtrack worked in the game.