It's become a cliche' now but I saw "The Sixth Sense" during the first two weeks of it's theater release and I'll readily admit I did not have it figured out. At the end, when her ring hits the floor and rolls away as the big reveal starts...I was totally mindfued. Floored me.
Similar to the Floyd story above....when Springsteen ended his reunion tour in 2000 with 10 straight shows at Madison Square Garden, I was lucky enough to attend two of them. While certainly not one of his "classic" songs, on June 22, he played "Secret Garden" for the first time since 1995. That song was was a huge part of the soundtrack of one of my most cherished relationships. We had broken up since but I wasn't over it...I thought she was my soulmate. When Bruce and the band launched into that, completely unexpectedly, I absolutely lost it. Cried like a baby for the entirety of it. Then a song or two later they played "Backstreets" which is my favorite Springsteen song. Quite the emotional roller coaster that night.