Something I've been meaning to do for a while now is sort text searches by relevancy. There are alot of examples in the Postgre documentation but I found them a bit vague to understand.
The way I was using it was a little different to the way they were trying to provide examples for, because my table already had a tsvector field which stored the information ready for searching.
This example below is based on the things I've learnt from Haitham Mohammad's blog.
SELECT table.*, ts_rank_cd(tsvector_field, plainto_tsquery('microsoft software')) AS rank
FROM table
WHERE tsvector_field @@ plainto_tsquery('microsoft software')
ORDER BY rank Assuming that your table has a field called "tsvector_field" which is pre-populated with text search information, the function ts_rank_cd() will create a ranking of the results which allows you to sort by relevancy. The trick is that you have to give it both arguments in your "WHERE" comparison clause in order to make it work.
[ Source ]
