Запрос SQLite LIKE & ORDER BY Match

Мне нужен SQLite-запрос, который ищет 1 поле только с использованием LIKE.

Основной пример:

SELECT name FROM table WHERE name LIKE "%John%" ORDER BY name LIMIT 10;

Проблема заключается в том, что я хочу, чтобы результат упорядочивался таким образом:

Если поле равно (например, "Джон" )
Если поле начинается с "John" (например, "John Doe" )
Если поле содержит "Джон" (например, "Джейн Джон Доу" )

Следующий запрос достигает ожидаемого результата, но медленный:

SELECT name FROM table WHERE name LIKE "%John%" ORDER BY CASE WHEN name = "John" 
THEN 1 ELSE 2 END, CASE WHEN name LIKE "John%" THEN 1 ELSE 2 END, name LIMIT 10;

Запрос выше медленнее (или я тестировал его неправильно), чем альтернативу использования 3 отдельных запросов (один для точного соответствия, один для начала и один для содержит).

Есть ли другие альтернативы?

Ответ 1

Попробуйте следующим образом:

SELECT name 
FROM table 
WHERE name LIKE "%John%" 
ORDER BY (CASE WHEN name = "John" THEN 1 WHEN name LIKE "John%" THEN 2 ELSE 3 END),name LIMIT 10 ;

Ответ 2

Для ваших тестов эквивалентности должно быть достаточно:

ORDER BY name = "John" DESC, name LIKE "John%" DESC

Предложения ORDER BY оцениваются слева направо.