The most memorable music pieces for me have been:

The music from Shadow of the Beast and the intro music to Leander, both by Psygnosis for the Amiga.

Most of the music from the Atari Jaguar CD game Battlemorph. It always fit the setting perfectly.

The music from the beginning of Rygar from the NES version.

The majority of music from the Halo series. Very mood setting and atmospheric.

I'm sure that there's loads of other soundtracks that I've really enjoyed in the past, but for some reason I'm drawing a blank at the moment. I either can't remember what game the music came from or I can't recall specifics enough to pull it out of long-term memory.