Weighted conditions in the WHERE clause of a SQL statement

Question

I want to find records in which some STARTS WITH field with some string (let them say "ar") OR this field CONTAINs the string "ar".

However, I believe that the two conditions are different from each other, because I limit the number of results returned to 10, and I want the STARTS WITH state to be weighted more heavily than the CONTAINS condition.

Example:

SELECT * FROM Employees WHERE Name LIKE 'ar%' OR Name LIKE '%ar%' LIMIT 10 

The trap is that if there are names that start with "ar", they must be approved. The only way to return a name that simply CONTAINS "ar" is if there are less than 10 names that START with "ar"

How to do this for a MySQL database?

Answer 1

You need to select them in 2 parts and add a preference tag to them. 10 from each segment, then combine them and take the best 10 again. If segment 1 produces 8 records, then segment 2 of UNION ALL will play the remaining 2

 SELECT * FROM ( SELECT *, 1 as Preferred FROM Employees WHERE Name LIKE 'ar%' LIMIT 10 UNION ALL SELECT * FROM ( SELECT *, 2 FROM Employees WHERE Name NOT LIKE 'ar%' AND Name LIKE '%ar%' LIMIT 10 ) X ) Y ORDER BY Preferred LIMIT 10 

Answer 2

Assign a code value to the results and sort by code value:

 select *, (case when name like 'ar%' then 1 else 2 end) as priority from employees where name like 'ar%' or name like '%ar%' order by priority limit 10 

Edit:

See Richard aka cyberkiwi answer for a more efficient solution if there are many matches.

Answer 3

Try this (you do not have a MySQL instance available for testing):

 SELECT * FROM (SELECT * FROM Employees WHERE Name LIKE 'ar%' UNION SELECT * FROM Employees WHERE Name LIKE '%ar%' ) LIMIT 10 

There are probably better ways to do this, but it immediately popped into mind.

Answer 4

My decision:

 SELECT * FROM Employees WHERE Name LIKE '%ar%' ORDER BY instr(name, 'ar'), name LIMIT 10 

Instr () searches for the first occurrence of the pattern in question. AR% appears before xxAR.

This prevents:

• Only a table scan should be performed 1 time. The unions and views do 3. The first two in the columns to filter out the patterns, and then the third in the subset to find where they are equal - because the join filters out the cheats.

• Gives a true look based on the location of the template. Wx> xW> xxW> etc.

Answer 5

 SELECT * FROM Employees WHERE Name LIKE 'ar%' OR Name LIKE '%ar%' ORDER BY LIKE 'ar%' DESC LIMIT 10 

Should orders work with a binary true / false value for the like, and if index'ed should benefit from the index