Suppose I'm searching product using 'Celebrating Silence' which pulls all product whose description, search keywords and product name contains the above phrase. But first I want to display the product whose product name is 'Celebrating Silence' exactly matching and then the other keyword matches. The actual product name 'Celebrating Silence' is showing in 5th page.
Check this question, it has a lot of theory.
There are 3 ways to search in Magento: Like, Full Text and Combined, the three with different pros and cons. You can also use Solr and set the attribute relevance in the search, but that is worth another question.
Another good resource is this article.
It's hard not to be sarcastic: IMHO when we talk about three Magento built in search options, then they are bad, poor and sucky. There's handful of third party extensions to remedy this but all they do is that they suck somewhat less. Right now the only alternative you have is to use third party software, such as SolR or Sphinx. Both of them require installation of indexing software and installation of commercial extension.
SolR is out of box supported by Magento EE. There's at least one third party extension that integrates it with CE as well, but my experiences with it is .. well, lets just say that it didn't work out between us. Sphinx is generally considered teensy bit inferior to SolR, but I got it up and running at first try and reindexing was lightning fast compared to SolR. There's also at least one third party commercial extension for Sphinx.
SolR and Sphinx both use some kind of fuzzy logic to determine what to display to a customer and both give pretty good results. Both are able to recognize common typos, such as Magento instead of Magento :), both give results in a fraction of second and both give you exact match as a first result (in case you wonder). Both require however the installation of third party software (not the extension, but full indexing service) and some system level knowledge.
Magento uses fulltext search and mysql relevance to determine products position in the result set. See http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html
This is the answer for similar situation: https://stackoverflow.com/questions/7227030/php-mysql-fulltext-search-order-by-relevance
So you may need to have the product name as a separate column in the index table to put more weight on it.