Yes for bioshock infinite!
I was thinking about my answer before I read the OP, and I quickly had 2 games in mind: bioshock infinite and Uncharted 2.
The intro to Bioshock infinite blew me away, esp during the part with the gospel singing when Booker first arrives in Columbia. I had chills the entire time. So hard to describe, but it was just such an amazing scene and experience.
Uncharted 2's intro speaks for itself. It was the first time I felt like I was the character in a blockbuster movie. The whole game is amazing, but the intro stood in my mind far above that of all other games until Bioshock Infinite came along.
TLoU is probably third. Can't think of any other overly impressive intros ATM.







