Prioritize word in SQL query - MsSQL

I think the solution with UNION or UNION ALL would be somewhat bad for performance, since two queries would be executed.

The @ademario solution is good, but the query might be simpler, that is, there is no need for subquery .

In addition, another interesting feature in search engines is to prioritize not only one field relative to the other, but also when both fields have the word.

See this query :

select tabela.*,
       (
         case when campo1 like '%fisica%' then 2 else 0 end +
         case when campo2 like '%fisica%' then 1 else 0 end
       ) as peso
  from tabela
 where campo1 like '%fisica%'
    or campo2 like '%fisica%'
order by peso desc

The column peso will return:

• 3 when word is in both fields
• 2 when it is only in field1 (priority)
• 1 when it is only in field2 (least important)

Functional example in SQLFiddle

Assuming it would be a query like this (it's good to always show an example for everyone to understand):

SELECT CAMPO1, CAMPO2
FROM TABELA
WHERE CAMPO1 LIKE '%FISICA%' OR CAMPO2 LIKE '%FISICA%';

and that its intention is to display first those that meet the condition of field1 before field2.  You can sort your results using a column with condition in the select and sort by it. see:

Select * from (
   SELECT CASE WHEN CAMPO1 LIKE '%FISICA%' THEN 1 ELSE 2 END AS ORDEM, CAMPO1, CAMPO2
   FROM TABELA
   WHERE CAMPO1 LIKE '%FISICA%' OR CAMPO2 LIKE '%FISICA%'
)
ORDER BY ORDEM;

This holds true for any organization that escapes from a simple sort order, even helps to change how to display information in the select according to their needs.

I do not know if I understand and if it would be the best way, but you could do something like that. I will exemplify the various forms I have understood.

I think what you wanted to say was this, a query that filters the results by a certain keyword in two fields. All results that have located the word in the priority field should come first.

SELECT campoA, campoB, campoC, 
 CASE WHEN minhacoluna1 LIKE '%fisica%' THEN 1 ELSE 2 END AS prioridade
 FROM minhatabela WHERE minhacoluna1 LIKE '%fisica%' OR minhacoluna2 LIKE '%fisica%'
 ORDER BY prioridade ASC

To query exactly one column column and another approximate column, and by ordering the exact results:

(SELECT campoA, campoB, campoC, 1 AS prioridade 
 FROM minhatabela WHERE minhacoluna1 LIKE 'fisica')
 UNION
(SELECT campoA, campoB, campoC, 2 AS prioridade 
 FROM minhatabela WHERE minhacoluna2 LIKE '%fisica%')
ORDER BY prioridade ASC

To prioritize results from the same column, start the word search in the first position (beginning of the text) of the other results with the word searched in other positions:

SELECT * FROM minhatabela 
         WHERE minhacoluna LIKE 'fisica%'
UNION
SELECT * FROM minhatabela 
         WHERE minhacoluna LIKE '%fisica%' 
         AND minhacoluna NOT LIKE 'fisica%'

From what I understand from your description, you could use the following select:

SELECT campos, 1 AS flag_busca FROM tabela where campo LIKE '%fisica%'
UNION
SELECT campos, 2 AS flag_busca from tabela where outro_campo LIKE '%fisica%'

The lines of the fields you want to "prioritize" will come with flag_busca = 1, the others with flag_busca = 2.

Remember that to use UNION you must have the same number of columns in the two selects.