SQLite sort only by first WHERE match

Question

I have a table that contains 3 columns: name, keywords, and weight, with these data

name	keywords	weight
Kevin	Any, Any, Any, Any	1
Hugh	Kevin, Any, Any, Any	1

Now I want to select from this table where name or keyword match a query and want to sort by name and weight, but the sort should be only for the where that matches the name not the keyword, for example using this query:

SELECT * FROM users WHERE name LIKE '%Kevin%' OR keyword LIKE '%Kevin%' ORDER BY name, weight DESC;

This query will return both Kevin and Hugh but Hugu will be at the first and then Kevin, What I need is Kevin to be returned first, so if the name matches the query do sort by name and weight if it's not but the keyword is matching the query then do sort by weight only.

So the question here, how can I sort depend on if the name match the query or not

Answer 1

I think you want the matches to name first:

SELECT *
FROM users
WHERE name LIKE '%Kevin%' OR keyword LIKE '%Kevin%'
ORDER BY (CASE WHEN name LIKE '%Kevin%' THEN 1 ELSE 2 END),
         weight DESC;