The Silent Hill series owes a lot of its success to its music, so that's definitely up there.
A more recent one for me was Shadow of the Colossus, how each giant seemingly had a separate piece of music accompanying it. It was just so epic and exciting, but also very reflective and lonely when the story called for it.
Also, any Metroid game. The most atmospheric video game music I've ever heard.
Oh, and though Portal probably won't be remembered for its ambient music, "Still Alive" may very well be the greatest song from a video game ever, so that should at least get honorable mention.