Wikipedia (http://en.wikipedia.org/wiki/CAP_theorem) - In theoretical computer science the CAP theorem, also known as Brewer's theorem, states that it is impossible for a distributed computer system to simultaneously provide all three of the following guarantees: Consistency, Availability and Partition tolerance.
There is a nice diagram available on the web (http://www.nijee.com/2011/02/golden-key-to-distributed-computing-is.html) - "Visual Guide to NoSQL Systems" which talks about which NoSQL DB fits where based on CAP theorem. This theorem was put forward by Eric Brewer. CAP theorem in brief states that a system can have only two out of three properties- Consistency, Availability and Partition-tolerance.
An application is considered to be consistent if an operation is performed fully or not at all. If the operation gets performed half-way, inconsistency gets created. To give you an example, there are many deals sites at present. Some of the deals are limited to a specific count say 100. Suppose you are the 100th consumer of the deal, you are about to pay for it and in the meanwhile someone else also selects the same deals and makes payment. Both of you cannot become the 100th consumer of the deal. So while you've confirmed to your selection of a deal, the second one should not be proceeded to make payment at all.
Availability states that the service remains available. Taking the same example as above, if you want to buy something in a deal but the site itself refuses to open, may be because of heavy traffic i.e.non-availability of service, is of no use.
Partitioning will not be needed if all your data resides and runs in a single box. But where the amount of data is huge it is very likely that there will be partition and data will be distributed. For a system to be partition-tolerant, it should respond correctly in every case except the case of total network failure.
In most of the NoSQL DBs, consistency is being compromised. Typically choosing one DB may not solve all your problems. Select one keeping certain features of your application in mind and feel free to go for another one for other modules. Also this doesn't mean removing relational database completely. Personally I always prefer to keep relational DB in my applications and use it at certain places where it really makes sense to use it!
In fact if your application is large enough, you can use a combination of NoSQL databases- utilizing the required features of each one of them, plus use a traditional database.