Usually I don't really venture out of metal/classic rock, but a few of the video games I've played really did have amazing music.
One of the reasons I loved playing GSC was because the music was always so great. Some of the tunes got boring, like surfing and battle music, but all of the tunes out on various routes were just so... it's hard to describe, but they put me in high spirits.
I also really liked a lot of the music from Legend of Zelda - The Wind Waker. Everyone complained about all of the sailing you had to do in that game, but I really enjoyed the sailing, in part due to the music that played while you sailed about. Probably the broad sound and major key really just made me zone out and really just stop thinking about anything. It's a lot different from the Pokemon music, though, since Pokemon had a lot fewer sounds with which to work. I really don't think it would have worked if it were the synthesizer sound instead of the orchestra. The music that plays while you're fighting the sandworm boss is pretty intense as well.
Also in some of the horror games, the music is really essential to the game. Usually just listening to that music will get really boring, but when you're playing the game, the sounds or lack thereof really makes the games what they are.