My favourites are from Morrowind and Oblivion.
Oblivion:
The Dark Brotherhood missions in general, but especially Whodunit? and Accidents Happen (the one with the minotaur head. I just like that for some reason)
Through A Nightmare, Darkly - You must go into a Mage's dream and find the pieces of his shattered mind.
I forget the quest name - You have to go inside a painting to find a painter and his magical brush, fighting Painted Trolls along the way. So bizzare.
The two 'secret' quests (that I know of) - These quests aren't quests per se, as they don't aqppear in your journal. They are interesting though and give you good loot. The first is the Horror Of Dive Rock. When you go to Dive Rock in the far NE corner of the map, you'll find a campsite there, with a book talking about two Nords hunting the Uderfrykte Matron (an Easter Egg referring to Bloodmoon). Close by is the Matron, a big half-invisible Troll. Kill her, and you get a powerful bow.
The second one is a treasure hunt in Pale Pass. You can find a chest (or barrell....some kind of container) with a note in it, something about stealing a ring from a warlock or something. You also find a key. What follows is a treasure hunt here you must find the chests hidden around Pale Pass and eventually find a powerful ring.
Also, all the Daedric god quests are interesting. I like collecting all the artifacts, just for bragging value.
Morrowind:
Again, treasure hunting. There's a huge number of unique artifacts that you can find. In my old game I found every single one, and put those that I wasn't using in my stronghold's vault.
The courtship side quest with the Khajiit woman in Ebonheart are nice. Not exiting mind you, just a nice change of pace.