Weighted conditions in the WHERE clause of a SQL statement

Question

I want to search for records where a particular field either STARTS WITH some string (let's say "ar") OR that field CONTAINS the string, "ar".

However, I consider the two conditions different, because I'm limiting the number of results returned to 10 and I want the STARTS WITH condition to be weighted more heavily than the CONTAINS condition.

Example:

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

The catch is that is that if there are names that START with "ar" they should be favored. The only way I should get back a name that merely CONTAINS "ar" is if there are LESS than 10 names that START with "ar"

How can I do this against a MySQL database?

Answer 1

You need to select them in 2 parts, and add a Preference tag to the results. 10 from each segment, then merge them and take again the best 10. If segment 1 produces 8 entries, then segment 2 of UNION ALL will product 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