基于SQL查询订购结果

Question

I currently query a table for items with a certain category. I have extended this query to also looks within the item description column too. My question is, how do i order results so that the category matches are first and the description is last?

`SELECT * FROM myTable WHERE `category` LIKE '%keyword%' || `description` LIKE '%keyword%'

In my above query simply adding a ORDER BY category will not suffice. The only way I can think of doing this is two separate queries with two separate result variables.

I'm using php and mysqli

Answer 1

It could be done like this

SELECT *
FROM myTable
WHERE `category` LIKE '%keyword%' || `description` LIKE '%keyword%'
ORDER BY
    CASE
      WHEN `category` LIKE '%keyword%' THEN 1
      WHEN `description` LIKE '%keyword%' THEN 0
END