Tempurpedic is the best I've seen. There are crappy versions of memory foam matresses, but admittedly, there are pretty good imitators as well. I don't have one yet, but I will be buying one soon. For the record, I know four people who have them (OK, two couples). They would eagerly attack angry, nursing badgers if it was the only way to save their Tempurpedic mattresses.
Select comfort beds suck. Every model is like sleeping on a plastic bag. If you want air, go to Wal-Mart and buy an Eddie Bauer inflatable air-mattress. They're 1000% better. (For the record, I'm a 32
)
There is a new breed of matrai (plural for mattress lol) out now. If you are interested in doing away with springs, but don't want to live in the 1970s with water or air, check out latex (unless you're alergic to it). It seems to be more firm than the memory foam, but if you like a firm mattress, latex might be the way to go. I'm pretty sure it's just a solid brick of the stuff. You might want to consider their delivery option since it's probably heavy as heck.
Remember: you will spend between 6 and 9 hours of every single day on this thing (unless you're a vampire). You might as well spend some green if your dream mattress requires it. I'm about to go pay mine a visit now, actually. Good luck!