In my opinion the best soundtrack I ever heard in a video game is in Divine Divinity 2. Not only the main theme, the one you can hear below in the video I attached. The entire soundtrack from this game is just amazing. I’m always interested in soundtracks from video games, because I think that the gameplay is 50% enhanced by the music.

Here is the main theme from Divine Divinity 2:

http://www.youtube.com/watch?v=H1-nxyEJGDE

I also have to admit that Assassin's Creed 2 soundtrack is good. It is performed by Jesper Kid and I really like to listen this song on my MP3 player when I’m upset.
Here is the song:

http://www.youtube.com/watch?v=O0i6YFrSs6c

The third would be the one from Fable. I placed it last because you need a special mood to listen this song. :)) Check this out:

http://www.youtube.com/watch?v=LSNgijFiUpk

Definitely The Legend of Zelda: Ocarina of Time. I've heard many great soundtracks from a ton of incredible games, but nothing does it to me as much as OoT. Not only was most of it conducted by a full orchestra, but the scores are beautifully simple, yet complex. And I would absolutely be lying if I tried to say that the nostalgia I have for the game itself didn't play a role in my appreciation for the soundtrack. Just hearing "Saria's Song" or "Zelda's Lullaby" is a trip "down memory lane" every time, despite that I don't play the game anymore. I mean, Zelda games generally have fantastic soundtracks, but never as good as OoT. It's really the game that started the trend.

I’m probably going to come off as incredibly lame for answering this question this way but the game with the best soundtrack for my money is Madden 2010’s soundtrack. They use all the best bit from the NFL Films library of many of those orchestral songs are one I heard when I was a kid growing up and watching the highlight movies and videos from the NFL Films vaults.

They remind me of a time in the NFL that I was not able to witness first hand because I wasn’t old enough and I think its sort of cool to be feeling that way while I’m playing a futuristic video game that imitates the actual game so much. There are also some hard rock and rap songs that are designed to get charged up. For the most part I don’t really notice the game soundtracks that much but that one stood out to me.

My favorite video game soundtracks have always been from the Tony Hawk Pro Skater series. The songs always keep you moving or grooving. Also, the games have a great diversity of artists from the alternative rock and hip-hop scenes. Below I have listed the soundtrack to the Tony Hawk Pro Skater 4 game. If you have not heard some of these songs before, I strongly suggest giving them a listen one day. My personal favorite on the list is De La Soul.

System of a Down - "Shimmy"
The Offspring - "Blackball"
AC/DC - "TNT"
Sex Pistols - "Anarchy in the UK"
Run-DMC - "My Adidas"
The Cult - "Bad Fun"
N.W.A. - "Express Yourself"
De La Soul - "Oodles of O's"
Public Enemy - "By the Time I Get to Arizona"
Iron Maiden - "The Number of the Beast"
The Distillers - "Seneca Falls"
Gangstarr - "Mass Appeal"
Goldfinger - "Spokesman"
Agent Orange - "Bloodstains"
Less Than Jake - "All My Best Friends Are Metalheads"
Flogging Molly - "Drunken Lullabies"
Rocket from the Crypt - "Savoir Faire"
Aesop Rock - "Labor"
Avail - "Simple Song"
Bouncing Souls - "Manthem"
Delinquent Habits - "House of the Rising Drum"
Eyedea & Abilities - "Big Shots"
The Faction - "Skate and Destroy"
Haiku D'Etat - "Non Compos Mentis"
Hot Water Music - "Freightliner"
JFA - "Beach Blanket Bongout"
Lootpack - "Whenimondamic"
Nebula - "Giant"
Toy Dolls - "Dig That Groove Baby"
US Bombs - "Yer Country"
Zeke - "Death Alley"
Muskabeatz - "I'm a Star" (featuring Grandmaster Melle Mel)
Muskabeatz - "Versus of Doom" (featuring Jeru the Damaja)
Muskabeatz - "Bodyrock" (featuring Biz Markie)
Jai Plus - "Bad Dreams"

For me, I keep thinking of series. (I hope no Old Timers are reading this, because they will crucify me for not mentioning the old games.)

I love the soundtracks from the GTA games, but that's mostly because Grand Theft Auto uses real music.

Then you have the Metal Gear Solid series. I not only love any music I especially appreciated

The tops for me has to be the Final Fantasy series of games. Final Fantasy VII in particular has great music that I still appreciate. My top 2 are Sephiroth's theme and the battle theme. LOVE EM! A lot of the music from Final Fantasy games are worth mentioning, but VII holds a special place in my heart, and not just because it was the first FF I ever played.