I'm not going to rank ones I would recommend purely for location, as although they were special, it was because of where they were - not the actual hotel; if that makes sense!
I once stayed at a Grand Hyatt in Cairo (long became a Hilton, then now the Grand Nile Tower, mr google tells me), which was on a little island off the Nile Corniche. It had a revolving restaurant on the top, and the food was pretty good, along with the views of Cairo at sunset. The lobby was probably the grandest I've ever been in, and it flowed through to a boardwalk on the Nile. Our room had a balcony onto the Nile, where we could watch the feluccas going by. Everything was marble, and the service was impeccable. Very clean, big rooms, and the most enormous bathtubs!
The Lebua at State Tower in Bangkok is gorgeous. It's got this amazing outdoor bar on about the 65th floor, which looks out over the city, and Chao Phraya River, and is incredible at sunset. The rooms are all large, with a big balcony - and I think they're all suites. The exchange rate means luxury at a reasonable price - but it's still a treat! The service is really attentive, nothing is too much; and we had some amazing food there too. So lovely....
I've also stayed in the Athens Hilton, which has incredible views across the city. The rooftop/outdoor bar is a massive plus point, and although the rooms are fairly nice higher end chain hotels, the Greek hospitality makes it special. It's also one of my favourite places to visit. :)
Nearer to home, Slaley Hall in Northumberland is a favourite. Lovely setting, beautiful food, and amazing staff. It's gorgeous without being snooty. It's not mad expensive, but we've visited 3 or 4 times now. I'd really recommend it.