Asus' has the quiet thing down. MSI is right on their heels (but, plenty of coil whine reports :|), but with a higher power use limit. Gigabyte can give great returns on OCing. Palit's seems to be between MSI's and Gigabyte's, but louder (or just harsher?). I don't recall where EVGA's fits in.
Actually it depends on the case. Some gamers like Blackened made a good point that sometimes the dB rating doesn't correspond to how quiet the card actually sounds in reality. The case's presence of dampening material such as in Fractal R4 can drown out some of the fan clicking noises.
I really enjoy sound videos of the GPUs as that allows me to actually hear how the card sounds at load. Computerbase did a review of 11 GTX970s:
http://www.computerbase.de/2014-10/nvidia-geforce-gtx-970-test-roundup-vergleich/4/
If you view the videos on an open test bed:
100% load
Quietest - Zotac 970 Extreme!
2nd quietest - MSI Gaming 970
3rd quietest - Zotac (small size) and Zotac Omega and Gigabyte G1
The Asus Strix 970 at load is actually one the louder cards. The Extreme, Gaming and G1 also run cooler than the Strix. They also show how the fan speed scales at 120 FPS, 60 FPS and 40 FPS.
Here is what separates the top 3 quietest cards: Zotac Extreme uses about 40W less power than G1 or MSI Gaming.
http://www.computerbase.de/2014-10/nvidia-geforce-gtx-970-test-roundup-vergleich/5/
Therefore, Zotac Extreme 970 offers the best combination of temperatures, power consumption, noise levels and performance of all the 970 cards out right now but it costs too much at
$410. The next best card on the list is MSI Gaming 970 but it uses substantially more power then the reference design, an aspect which
jfp555 said is important to him.
Thus, if we still want to pick a relatively quiet 970 but one that uses little power, the next best card is the Zotac (small size) at
$330. Unfortunately that card is getting some
negative reviews from overheating/crashes, which brings us to Zotac Omega, MSI Gaming and Gigabyte G1. Out of those 3, the MSI Gaming doesn't spin the fans up to 65C and is quieter than the G1 and is cheaper than the G1 or Omega.
*** And we haven't even touched on Asus' horrible VGA RMA experience.