Prioritize word in SQL query - Mssql

Question

I have the following task:

Make a query where I have to give preference to the word passed as parameter.

I search two fields with one OR, but I need to prioritize the consultation by the first field.

For example: if I search for "%physica%", currently brings the word physica in the two fields, but the most important is the first field, because as I am doing a OR, in the first column displaying the result appears physical at the beginning and comes a lot of other results, and then more "physical", "physical education".

I don’t know if I explained it right, but I hope someone has an idea to fix it, I’ve looked like crazy.

Answer 1

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

The solution of @ademario is good, but the query can be simpler, that is, there is no need to subquery.

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

Look at 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 the word is in the two fields
• 2 when it is only in field1 (priority)
• 1 when she is only in the field2 (less important)

Functional example in Sqlfiddle

Answer 2

Assuming it would be such a query (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 exhibit first those who meet the condition of the field1 before the field2. You can sort your results using a column with condition in 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 goes for any organization that evades simple sorting, it even helps to change the way of displaying information in select according to your needs.

Answer 3

I don’t know if I get it and it would be the best way, but you could do something like that. I will illustrate the various ways I understood.

I think what you meant was this, a query that matches the results by a certain keyword in two fields. All results that have located the word in the priority field, must 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 consult exactly one column and another approximate column, and sorting by 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 of the same column started the word searched 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%'

Answer 4

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_search = 1, the others with flag_search = 2.

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