As for me, I can't pick one. Here are some of my favorites:

Anachronox
Unreal Tournament 2003
Need For Speed: Hot Pursuit 2
GTA 2

Last but not least is Deus Ex, especially with the Russian bootleg translation, which creates some special atmosphere (so bad it's good).

Give it a try, even if you don't understand the Russian speech:

A BOMB

Homeworld

-Endless Space II

https://steamcdn-a.akamaihd.net/steam/apps/256685332/movie480.webm?t=1510856000

btw, can anyone tell what song is that ? :D its so good!



-Phantom Doctrine

https://steamcdn-a.akamaihd.net/steam/apps/256725540/movie480.webm?t=1534260814

The intro of Legacy of Kain: Soul Reaver. In both English and Spanish.

I have 2:

Command and Conquer: Red Alert

and

X-Com UFO Defense

Panzer Dragoon Saga

Jet Set Radio: Future

Pathologic

Rule of Rose

Shenmue

Dwarf Fortress

Arcanum: Of Steamworks and Magick Obscura

Flashback

Another World

Legacy of Kain: Soul Reaver

Nightmare Creatures 2

Skies of Arcadia

Spawn: In The Demons Hand

Thief

Carmageddon or Diablo 2

I'd probably say Fallout 1. It perfectly set up the mood for the rest of the game, which should be pretty much the point of every intro sequence. Plus, Ron Perlman narrating never gets old. We need more Ron Perlman in our video games! ;) "Die Waffen legt an!" *followed by head-banging* ;) It's funny how some music makes games really stand out. I loved the old C&C's soundtracks, yet I would never listen to such on their own. *shrug*

I prefer more minimalistic intros.

For example, Final Fantasy (NES); as soon as you choose your characters' classes and names, the game throws you right outside the starting town.

Super Mario Bros. is another good example. There's an attract mode on the title screen, but once you press start and choose 1 player, the game starts immediately.

Sonic the Hedgehog, on the other hand, is rather abrupt. There's no press start message on the title screen, so when you press start, you are expecting to see some menu, but the game just abruptly starts instead. It's rather startling if you aren't used to it.

Only certain tracks from any CNC where enjoyable on their own, but yeah the music depends on the game to "work" in the case of CNC

NOLF

Evil Genius

and to a lesser extent

Civilization 4

Far Cry 2

and ah yes, the two Fallout (Ink Spots and Armstrong) would qualify aswell. Also Cannon Fodder. But if we start with Amiga intros...

Indeed. This makes me also think of FlatOut 2. I'd never be caught dead listening to the included songs outside the game, but they're almost a perfect fit while playing.

Baba Yetu is probably the most memorable thing of the whole game, but that's not the actual (unremarkable and forgettable) intro, so...

Yeah, i din't mentioned Fallout 1&2 and Arcanum. But it's obvious that those are the best of all time. :)

It is, but in the main menu screen. That's not the intro. ;)