Simple topic, simple purpose: Name ONE tune that takes your breath away, gives you goose bumps, or otherwise makes you completely ecstatic. The purpose is to introduce new tunes we might have overlooked (although I’ll be frank; mine is very well-known).
The Lonesome Boatman
http://nl.youtube.com/watch?v=s3mw-h7UQ68
I seldom get goosebumps, except when I hear something that makes me nostalgic…
Generally speaking though, you can’t go wrong with Lonesome Boatsman. It’s a good tune.
You seem to have in mind slower, non-dance tunes, a song you might appreciate is The Maid of Coolmore,
The Sunset is also good (tune).
There are also lots of slow airs (maybe Maid of Coolmore is an air, I don’t know) that are a delight to listen to.
For me, two are Amhran Dochas (as Dochas on Chieftains 7), and Chuimhne an Phiobaire (first tune on #6 The Coombe, same album…love it also when the pipes come in on Repeal of the Union)…Sean Potts. Those two tunes are the reason I picked up a whistle in the first place. Both still have a really physical effect on me, every single time; stop me dead in my tracks.
On the other hand, I just bought a very highly rated CD by a very famous whistler, based partly on reviews where people were obviously affected…to me it’s very, very close to muzak. Can’t figure where they were coming from. They were overwhelmed, I can’t make a connection to it at all. It’s like we are listening to two different cd’s. Horses for courses, Iguess.
Och, I’m afraid you’ll get some push-back on the Lonesome Boatman. 
It’s rather in the same category as Danny Boy. Overworn tunes/songs that punters love to ask for, and players balk at playing. Supposedly, Finbar Furey re-worked the melody of “Fear a Bhàta”. As a whistle player, it seems to me that the tune gives you little to work with without some sort of airy chord accompaniment, and it doesn’t stand well on its own.
On the other hand, some drunken pub punter once offered me $20 to play it for him, and I’ll admit I took the fric. A happy customer …
I’m a complete sucker for certain chord changes and modulations in context. For example, the momentary IV of IV can give me chills. The old trick of modulating up a key (e.g. G to A) can be exciting. Cathie Ryan’s setting of Dougie MacLean’s Garden Valley, the instrumental break that modulates from Db to Eb to C, and the entrance of the Eb whistle. Brrrr. I’m a softie.
Added: I just re-listened to my recording of TLB (The Fureys’ Finest). And I swear, it sounds exactly like the Ennio Moricone sountrack to a Sergio Leone spaghetti western, strummy guitars and ocarina and all. Plus sound effects of ocean waves crashing and seagulls squawking. I wonder if this recording was really a joke, Finbar taking the mickey out of everyone. Goosebumps maybe, for the wrong reason … 
You guys are cheatin! Haggis said ONE tune!
Kinda like those threads where they ask what ONE whistle you would have on a deserted island, and people name three!
I, at least, know how to obey the RULES!
My one song would be “Be Thou My Vision.”
…or, perhaps, “Marcus Hernon’s Air.”
Oops!!
I was just about to say Marcus Hernon’s Air. Joanie Madden is unbelievable with that one. I also love the Mist Covered Mountain.
Amhran na Leabhar
I couldn’t agree more to that. TLB sounds rather horrible when it’s just a whistle, but some light chord progression suddenly completely turns the tune around. I figured it’s because the rhythm section pops in right after the intro (usually, anyway), which is where the song really gets me going. Take out that rhythm, and you take away the charm of the tune.
Ooh! Mist Covered Mountain is a good one!
Tommy Peoples’ An Feochan/The Gentle Breeze, especially the late Frankie Kennedy’s rendition on one of the Altan albums which made it into a flute-repertory item…
TLB is the worst kind of schmalz - and I’m sure Finbar & Co. knew exactly what they were doing and were not at all tongue in cheek about it - it’s serious commercialism, like most of what they do/did… for xxxx’s sake, why has probably the C20th’s most gifted piper spent most of his career doing that sort of thing and not just main-lining the pure drop??? (Rhetorical question!).
Sorry to bang this one again, but “songs” in the thread title when the OP makes it clear the OP-er is thinking/asking about tunes, NOT songs per se? 
(Plenty of the latter that can raise the old goose-flesh when well sung! I offer Karine Polwart’s Marianna by way of example.)
TLB may be awful in terms of cheesy production. But the whistle-playing has a fierce sound, and is quite full of expression. In the recording by The Fureys, that is. I like it.
Such a Parcel of Rogues in a Nation
Duncan Johnstone. Especially with a good bangy guitar accompaniment. It’s a scottish hornpipe–One of those tunes that makes a person want to go out and do something heroic and impossible.
Gates of Avalon - http://www.youtube.com/watch?v=gLDNNnrlFko (no original tune on youtube, only mine)
Hey, guys, give the links to your tunes!
I was learning The Dark Island when I got the phone call that my dad passed away. That tune always evokes a little something extra in me. And they always let me start it at session.
Thanks for the encouragement. In all honesty, though, if fishing for an apology from a whistling youngster for (1) actually liking TLB and (2) having a habit of saying “songs” instead of “tunes” at times is the best you can do, my prediction is this is an unfortunate day for fishing.
I do thank you for the suggestion of An Feochan/Gentle Breeze, though. That one is indeed marvellous.
No apologies sought or required! MTGuru warned you about TLB! We like what we like and that’s fine, as long as we don’t expect or set too much store by others’ agreement/shared taste, though consensus opinions and higher “good taste” (whatever that is!) are both relevant. (Haggis might be a good case in point! 
) No discouragement was intended either, by myself or others who expressed similar feelings - leastways, not on playing music!
As for linguistic murder, well, that’s another matter. Songs by definition have words and are sung with a voice (unless you’re Mendelssohn!). The air, melody or tune of a song without its words being sung is… a tune, albeit a song-tune! Melodies (especially ones for dancing) that have no association with lyrics cannot sensibly be termed “songs” (unless there is some rider, as in Mendelssohn’s “Songs Without Words” for piano), and well/long established normal usage in trad music circles is to call them “tunes”. As songs are also important in trad music, there is a serious distinction to be maintained and understanding to be preserved/sought.
Oh, what’s the difference? Toons, aires, songues, gigs, reals… They’re all musics. Not rocket surgery, is it?
I don’t know about that. A rocket scientist dedicates several years to achieve his knowledge, but there are musicians who’ve gone on far longer than that, and are still sometimes struggling, and finding new ways to improve.
In some ways, music is probably more demanding and advanced than rocket science. 