Prioritize keywords found in the title in the sql search

Question

Hello, I have a table 'videos' with 3 fields 'name', 'Description' and 'categories'. When doing a search I want to appear at the top the videos that in the 'name' have more of these keywords, for example if I search "beautiful house" a video with name 'HOUSE in BEAUTIFUL LAGOONS' has to stay more at the top than a video with name "sao paulo CASA" and description "look how PRETTY she is"(as I do the search looking for the keywords in name and description).

Does anyone have an idea how to do this?

Answer 1

This subject may be far more complex than it appears to be.. The relevant search, so to speak.

But to answer your question more directly, I would suggest using the UNION clause .

SELECT * (<condicoes com alta prioridade>)
UNION
SELECT * (<condicoes com um pouco menos de prioridades>)
UNION
SELECT * (<condicoes com menos de prioridades ainda>)

Or, another suggestion is to use a ranking variable

SELECT t.*, [ranking expression] AS rankings FROM () AS t ORDER BY ranking DESC