Marriott Niagara, room with Falls view! Oh yes, OP's thread reminds me of our visit there. The view was awesome, particularly at night and during sunrise. Misty the whole time. The view is simply fantastic.
Off hand, I can think of Frankfurt Marriott (high floor, of course). It has a metropolitan kind of view; Nagoya Marriott Associa, which has (depends on your room location) castle view, view of train tracks where bullet trains glide in and out of station; JW Hong Kong used to have great ocean view before the newly built high-rises made that history.