Reputation:
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
Upvotes: 0
Views: 50
Reputation: 7
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
ELSE 0
END CASE
DESC
If you would like to broaden that SQL query and preserve these ordering, you can utilize:
SELECT *
FROM myTable
WHERE `category` LIKE '%keyword%' || `description` LIKE '%keyword%'
ORDER BY
CASE
WHEN `category` LIKE '%keyword%' THEN 1
ELSE 0
END CASE
DESC,
CASE
WHEN `description` LIKE '%keyword%' THEN 1
ELSE 0
END CASE
DESC
Results, what are both description
results and category
results, will be on begin - as it was not specified, I suppose, that so it is requested or accepted.
Upvotes: 0
Reputation: 534
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
Upvotes: 1