The General Adjoint Functor Theorem (Category Theory) states that for a locally small and complete category $D$, a functor $G\colon D \to C$ has a left adjoint if and only if $G$ preserves all small limits and for each object $A$ in $C$, $A \downarrow G$) has a weakly initial set.
Could someone help by giving an example of a functor $G$ that preserves all small limits but has no left adjoint?
On
Martin's answer is probably the one you want, but here's another marginally trivial example.
Let $\mathcal{C}$ be a category and let $\mathbf{1}$ be the terminal category with only one object and one morphism. The unique functor $G : \mathcal{C} \to \mathbf{1}$ obviously preserves all limits, but $G$ has a left adjoint if and only if $\mathcal{C}$ has an initial object. Dually, $G$ preserves all colimits and has a right adjoint if and only if $\mathcal{C}$ has a terminal object.
A nontrivial example is mentioned in MacLane's Categories for the Working Mathematician , on page 123: consider the category $\mathbf{CompBool}$ of complete boolean algebras. The forgetful functor $\mathbf{CompBool} \to \mathbf{Set}$ has no left adjoint, but preserves all limits ($\mathbf{CompBool}$ is also small-complete). The reason is that, given a denumerable set $D$, one can construct an arbitrarily large complete Boolean algebra generated by $D$ (a fact that was apparently proved by Solvay in 1966), and so the solution set condition in the General AFT fails.