Its not just the quality of music, its how you use it that counts. I've heard some really great music in games that I'd have no problem listening to as a CD but if it doesn't fit in or is too repetitive then it just gets irritating.
examples:
Azurik, Rise of Perathia had fantastic music but it was a LONG game and you spend ages in each region so the music gets old fast. Also it stayed the same whatever was going on. Dynamic soundtracks help to create interest.
Voodoo Vince had some really good music and some really bad and it all got repetitive after a while.
Some greate game music can be found at
http://soundtracks.mixnmojo.com/ - its got all the soundtracks from all the Lucasarts games. Many of these are as good as any movie soundtrack. Grim Fandango is stylish and great and The Dig (despite being a crappy game) is fantastic.
Still, the greatest game music ever has to be Tetris.