The Inner Light
In the episode The Inner Light, Picard comes in contact with an enemy probe, which links him to a faraway place and time. To Picard, it as if he as lived a lifetime. To his crewmates back on the Enterprise, he is unconscious on the bridge and attempts to revive him prove futile.
Besides the story and the acting of the episode, I really love the music. There are two versions available from iTunes, the standard version and the orchestral version. I prefer the latter, but they are both worth a listen (even if only the sample).
Orchestral Suite from the Inner Light from the album "The Best of Star Trek: 30th Anniversary Special!" by Jay Chattaway
By the way, the prop of the ressikan flute Picard played in this episode was one of the highest bid items in the recent Christy's auction.