I remember being surprised by, and liking, the grain/noise in Black Swan. It's in your face and obvious. This rates for me as "good-looking grain" in the sense that it's well-adapted to subject matter (presumably something to do with character's madness or mental anguish) rather than in the sense that the grain in itself is beautiful. Matthew Libatique, the cinematographer, deliberately introduced noise during the shooting (at times using DSLRs with high ISOs), and then added more noise in post.
Other contexts where noise might be a good choice -- when faking a black and white film (video can look too clean), when the topic is grungy, when it's supposed to be a found-footage made-on-consumer-camera noisy-blacks look.
In terms of examples of good-looking lack of grain/noise -- maybe some of the TV series shot on Red Ones (Misfits, Sanctuary), and some feature films shot on digital (Avatar), because less grain (compared to film) contributes to a sort of futuristic or high-tech feel (CSI NY lab scenes), or at times a dreamy feel (check out some of the slow motion footage shot on Red Epic).
Or what about animated films, cartoons? Don't these look better better because they're clean?
I love the grain and off colour balancing on stock used in indie films like Chungking Express (and thus basically any Wong Kar Wai/Christopher Doyle shot movie/advert)
I've always loved the thick grain in Fight Club. According to imdb:
"When the film stock was processed, several techniques were applied to alter the look of the footage and increase the 'grubbiness' of the image. Under the supervision of director David Fincher and director of photography Jeff Cronenweth, the contrast was stretched, the print was underexposed, re-silvering was used to increase density, and high-contrast print stocks were stepped on the print to create a layer of 'dirt', which Fincher likens to a "dirty patina."