The Sleep Number beds are sold only at their stores, as far as I know (well, you can order them online also). We have one. I like it OK though if I were to replace it right now, I might think of another option - Typo Knig has had a lot of trouble getting a setting he likes and now it’s soooo squashy that the whole bed is getting, well, a tad lopsided. On the plus side - it’s very light weight, and if you move, it disassembles quite easily. No problems with getting it around corners!
We used to have a soft-side waterbed. LOVED it. However, I think the quality has really declined in recent years; when we had to replace the liner for the 3rd time in 5 years (after the first one lasted 5-6 years on its own) we got fed up… hence the sleep number bed. It’s delightful on a cold winter day though - they come with heaters that can make the bed lovely and warm.
As far as pillowtop mattresses: supposedly the pillowtops can get flattened and lose their comfort. This is just what I’ve read - no direct experience with it. Presumably this could be sorted out with a memory-foam topper?
Most mattress stores have a variety of options to try. One near us has the Tempur-pedic (memory foam) mattress in addition to numerous traditional ones. Consumer Reports suggests (IIRC) a 15-minute test. Lie on the mattress for 15 minutes a the store. They said that very few people changed their minds about a mattress (as in, if they liked it after 15 minutes, they generally still liked it after several nights’ sleep).
Personally, I hated the Tempurpedic when we tried it in a store. I know people who LOVE theirs though.