Summary (after a few edits).
If you want cheap but passable:
Waves Audio Grand Rhapsodie
AIR MINI
Ivy Audio Piano in 162 is free!
Otherwise, first buy Pianoteq and then if you have the cash to burn, work out what else you need. No need to buy more than one of each category:
Soft and intimate cinematic:
Native Instruments Una Corda [simply amazing]
Spitfire Audio Olafur Arnolds Toolkit [also nice]
Spitfire Audio Felt Piano
Big and dramatic cinematic:
Spitfire Audio Hans Zimmer Piano [big soundscapes, single piano, insane number of mics]
Native Instruments Giant [lots of drama from very variable sounds]
Classical detail in playing:
SonicCouture Hammersmith [My personal goto]
Garritan CFX
Vienna Imperial [super detailed, super expensive]
Sampletekk Black Grand 2 or TVBO [nice and not too pricey, UI looks ugly]
Other character (ok, so you might need more than one of these for specific things depending on what you do):
Soundiron Emotional Piano [Designed to tug heart strings in a certain way but lacks something for me]
Cinesamples Piano in Blue [if you need that blue sound, this is the only place to get it.]
Native Instruments Alicia's keys [Meh...]
Spectransonics Keyscape [infinitely more than just a piano]
8DIO Estonia [unusual with a hint of exotic]
Art Vista Malmsjo [old, smooth, warm]
Sampletekk Rain Piano [beat up upright - there are a bunch of these around]
Sound Dust ShipsPiano3 [Pendle always delivers from the left field]
Cinematic Studio Piano [erm...]
Effects
Audio Imperia Klavier and Red [modern sound design]
Spectransonics Keyscape/Omnisphere [you can really change stuff up with this combination]
After that then you are in buying for pleasure mode. Consider the rest of the others and the below (in rough descending order of awesomeness. Actually, I have no idea of ranking of some of these. Indeed, I don't have every on but have listened to as much of the others that I can find and read many many threads and reviews. Some-much-of-a-muchness products seem to have landed in the middle. It really depends what you like.)
Bechstein Digital Grand [next paycheque will go here]
Synthology Ivory II [very accurate sounds]
VI Labs Audio Ravenscroft 275 [workhorse but never makes it to final mix for me]
Imperfect Samples (Lots of them) [beautifully all over the shop]
Production Voices Production Grand 2
EastWest QL Pianos
Galaxy Vintage D [may be in playing category above for some, nice samples but too few of them in my view]
UVI Grand Piano Collection
Accoustic Samples (various)
VI Labs Audio Truekeys
Orange Tree Rosewood [nice but goes thin sometimes]
8DIO (1990 Studio, 1969 Steinway, 1928 Steinway)[I don't own these - reports differ wildly]
Arturia V [Not really there yet. Will no doubt improve]
Sampletekk WG2
Sonivox Eighty Eight [underrated]
Native Instruments Definitive Piano Collection [never managed to make these feel special]
XLN Audio Addictive Keys [lots of people rave but i don't...]
WavesFactory Mercury [disappointing to me. Dull high range, HQ mode squeaks and spits on my system (i7 970)]
Others that I don't know enough to comment on.
Fluffy Audio Scoring [don't know it, personally - anyone else wish to comment?]
Impact Sound Works Pearl Concert Grand [don't know it, personally - anyone else wish to comment?]
Pro Audio Vault Bluthner One
Cinesamples Abbey Road Uprights[don't know it, personally - anyone else wish to comment?]
This is a summary of the state of the market.
Everyone has an opinion. One of the other commenters has described my list as "questionable" and I absolutely agree. Question away. This is a highly subjective personal opinion about a highly subjective subject. Hell, I doubt even the categories above will mean the same things for everyone. For instance, I have a passionate hatred of having too few velocity layers which does not seem to be universally shared. Also, everyone gets attached to things - sounds, products etc. particularly those they buy and use. People are making different sorts of music with differing keyboard and programming skills, have affinities to different real-world pianos, are using different monitors/effects/controllers, have different needs in terms of flexibility and different budgets. Another total wildcard is "mixability" which depends so much on the particular situation that to me every template needs a new rehearsal for the piano parts. There is huge overlap between what individual products can do also, so different people will have different combinations to cover the same territory.
If anyone wants to "correct" me/make the list better, let me know.
Anyway...