It kind of depends on what criteria you are using...
As for trees I'll see around here:
It is hard to beat the size/'spectacularness' of white oak and bur oak. I'd have to say these are my absolute favorites, but that doesn't mean I try to plant these more than others...
I do like the shape of pin oaks, but I see to many of those chlorotic because they are planted in alkaline soils.
I like hackberry because it is not over-planted, can take a variety of sites, and is a nice looking tree...but there aren't many hackberries that I would consider "extradordinary" trees.
There were a few mentions of shagbark...I agree that they can be beautiful tall trees with nice shape and great fall color. However, they are not as fun to climb--especially when you have to squeeze between tight braches. They are also a little more fragile when it comes to root damage.
I also like sugar maple because of its nice looking crown and color. A good climber too.