Some genres work better in SP. In other genres, MP can work, but creating good SP is much easier.
I suggest that we list all exceptional SP-only games on this thread, while we wait for Johan or Kristoffer to come in and answer Argitoth's question.
Cave Story (freeware playformer with plot)
Master of Magic (MP only via 3rd-party patch)
Monkey Island games (2d adventure)
LOOM (2d adventure, with music)
Okay, all 2d adventure games
Portal
Shift 1-3 (flash games available on e.g.
Kongregate)