I'd say Bioshock has the best intro ever.
It's absolutely perfect in every way. From the crash, to finding the old lighthouse with faded music playing, to Andrew Ryan's presentation of his objectivist paradise, to finally beholding the city itself. Everything flows brilliantly, the music is spot-on for every instance in which it is used. A great intro to a great game.
F.E.A.R also had a great intro. It brilliantly sets the scene for the entire game, and instigates a desire to understand what's going on. It's just a shame that we don't get to hear Peter Laurie (Fettel's voice actor) in the opening. Monolith is great at making really neurotic characters, and they always pick the best voice actors for them.