A cover song that I'm dying to hear but haven't quite yet: Sixpence's cover of Sam Phillips' "I Need Love." The original is wonderful... the quintessential Sam Phillips song.
I love that song. I stumbled across it quite by accident when I was first learning how to use Napster.
Some of my favorite covers that put a new spin on the original:
Barenaked Ladies - Lovers in a Dangerous Time (orig. by Bruce Cockburn)
David Crowder Band - Obsession (orig. by Delirious?)
dc Talk - In the Light (orig. by Charlie Peacock)
Jars of Clay - Rose Colored Stained Glass Windows (orig. by Petra)
Jennifer Knapp - Diamond in the Rough (orig. by Shawn Colvin)
Rebecca St. James - You're the Voice (orig. by John Farnham)
Rebecca St. James - Let My Words Be Few (orig. by Matt Redman)
Smash Mouth - I'm a Believer (orig. by The Monkees)
The Benjamin Gate - Overkill (orig. by Men at Work)
The Echoing Green - Accidentally 4th St. (Gloria) (orig. by Figures on a Beach)
Third Day - Agnus Dei (orig. by Michael W. Smith)
Some favorites that pretty much mimic the original style, but that I think are well done:
Audio Adrenaline - Free Ride (orig. by Edgar Winter Band)
Circadian Rhythm - Gloria (orig. by U2)
Human - Bullet the Blue Sky (orig. by U2)
Kevin Max - Hallelujah (orig. by Leonard Cohen - live cover)
Polarboy - Where the Streets Have No Name (orig. by U2)
Some covers that are just plain bizarre:
Aleixa - The Reflex (orig. by Duran Duran)
Cake - I Will Survive (orig. by Gloria Gaynor - yeah, I like it despite his use of the "f-bomb")
Eric Champion - Every Heartbeat (orig. by Amy Grant)
Fanmail - I Want It That Way (orig. by Backstreet Boys)
Five Iron Frenzy - It's Not Unusual (orig. by Tom Jones)
Ghoti Hook - Friends (orig. by Michael W. Smith)
Nickel Creek - Spit on a Stranger (orig. by Pavement)
Relient K - The Pirates Who Don't Do Anything (orig. by VeggieTales)
Sixpence None the Richer - Dancing Queen (orig. by Abba)
The W's - Country Roads (orig. by John Denver)
This Train - Baby Baby (orig. by Amy Grant)
And then there's my favorite "unintentional cover":
Kutless - Tonight (orig. "What If" by Creed)
Just kidding... that last one sucks! HA HA!