[Mr Guy]

Ah! Now that's a very different question altogether!

Fixed lenses don't really relate to DSLR focal lengths in an easy to compare way. For taking extreme close ups, you'll be happiest with a "macro" lens, which isn't directly a product of the focal length like you may think it is. "Macro" in camera terms means that the size of the image on the sensor of the camera is the same as the size of the subject in real life. So when you think about it, that means you can blow them up HUGE when you view or print the image. There are ALSO some lenses that call themselves macro lenses that aren't really one to one size wise. They are typically 1:2 (or half as large on sensor) or 1:3 (a third as large). They can give decent results, but for those truly mindblowing close ups, 1:1 is probably worth the money.

I'm not an expert on macro lens, but I do know that Macro has more to do with their optical properties and their minimum focus distance than it does their focal length. I know they also come in all different focal lengths. I'm GUESSING that that the reason for that is that different lenses allow you to get your macro shot at different distances from the subject.

If extreme close ups are your thing, then what you want is a macro lens in general. What you do with the rest of your time more or less determines what kind of macro you want.

What you want as an every day walk around lens is an entirely different question. It's a hard one to tackle for someone going from a super zoom to a DSLR, because you're used to having one camera and lens that can solve pretty much all your problems. With a DSLR, you get much more power and much better results, but it comes at the cost of less flexibility. There are several different opinions on what makes a good "every day" kind of lens.

1) Maximum flexibility. There are super zooms that give you the most flexibility, but they can be heavy, and aren't necessarily the best quality. This would be something like a Nikkor 18-200mm.

2) Best bang for the buck. It's becoming very popular to learn what kind of lenses you really want by deliberately limiting yourself to a single focal length until you can understand what that lens and camera can do inside and out. A very popular way of doing this is to either use the kit lens that generally comes bundled with a camera (usually a 18-55mm or a 50-200mm) exclusively until you find out what you can't do with it but you really want to (for example, your macro shots).
Another very popular way to do that is to buy a single focal length (called a "prime" as opposed to a "zoom") lens that's higher quality than an inexpensive zoom lens would be and exclusively using it. Again, the idea is to force yourself to learn by discovering what your personal limitations are. The most popular length is 50mm, mostly because that USED to be the normal lens (as explained above) and because Canon and Nikon both offer very inexpensive reasonably good quality 50mm (nicknamed "nifty fifty") lenses. Pentax, on the other hand, offers compelling reasons to go closer to a real normal lens on a APS-C camera by offering a variety of extremely good primes like a 40mm pancake lens (it's really, really thin).