Best music I've ever heard in a game:
Go to this link and check out the song titled "Kelethin" (probably best to just get the "Alternative" version which is more modern with better instruments - although the original has a bridge in it and is a bit longer I think). Click it and then click again to download the midi. It runs in Windows Media player. It will download in about 1 second. Well worth it:
PC Games Music! Free Midi Downloads!
I'm not sure whether it would translate to you just sitting here looking at a Hexus page and listening to that. Its a brilliant piece of music, but what made it extra special, is how well it fit where you were at that point in the game.
Besides that and some others, I was really impressed with the music in Monkey Island 2 (the song titled "?"). And I noticed someone mentioned Red Alert, I really liked the music in Tiberium Sun (the old game, not the modern Tiberium Wars).