DEMO is less than half over, so it's too early to talk about my own unofficial Best of Show award...but so far, I haven't seen anything that impressed me more than Riya. This photo sharing/search site does something quite remarkable: It uses artifical intelligence to recognize people by their faces, so it can help you find friends and family in your images.
Riya's bag of tricks is pretty big--besides looking at facial features, it also examines people's clothing to help recognize individuals in multiple pictures. It does subtle analysis of the timestamps on photos to help it make further intelligent guesses about who's who. And it also does optical recognition of signs and other text in pictures (if you're wearing a nametag, it may be able to read it).
None of this works absolutely perfectly, and the Riya exec I talked to admitted that there are times when the technology will think that one person is someone else, or will fail to find a given person at all. But you can train the service by telling it when it's right and when it's wrong, and when multiple people form a community, it analyzes all their photos as a pool for better results. It certainly looks vastly easier than manual sorting and/or tagging.
I haven't tried Riya myself yet, but it demos extremely well. It's still in a private beta, but the company says it'll be in open beta in a couple of weeks. I know I'll be in line to try it out...