Samsung Spinpoints are the quietest drives. Seagate drives comes in as a close second.
As it has been mentioned before getting drives with only 1 or 2 platters will greatly reduce noise and heat. Seagate currently holds the title for having the densest platters available at 188GB per platter. A Seagate 160GB 7200.10 will have just one platter. A Seasgate 7200.10 300GB will have only two platters.
Some people who are fanatic about having very quiet drives will use 2.5" notebook hard drives instead of the 3.5" HDDs. But they cost more, and have slower throughput.
I had two PATA drives and two SATA drives running in the same machine for a while and I could always tell when I was accessing the PATA drives by the crunching sound with disk access. I noticed a huge noise difference just switching from PATA to SATA. I've used seagates, maxtors, and western digitals and IMO, western digital are the quietest. I am running nothing but WD's and I get more noise from the psu fan and nary a peep from the hard drives.
Yes, SSDs would be the most silent choice and I actually might consider one (for Windows and the programs, probably..and the swap file) when I buy my next computer..they're damn expensive currently anyway. Prices will hopefully drop though.