I have a table with a column containing productnames.
I want to sort it by productname ascending, BUT i want the productnames containing the word "accessory" to be sorted LAST.
How can this be done?
1
2 Answers
This is a common way to do this:
SELECT * FROM my_table
ORDER BY product_name LIKE '%accessory%', product_name
or
SELECT * FROM my_table
ORDER BY LOCATE('accessory', product_name), product_name
The clause product_name LIKE '%accessory%' is a Boolean expression, which returns 0 or 1, so all the rows that don't match will return 0 and will appear earlier in the sort order.
LOCATE('accessory', product_name) works similarly, but will return 0 for no match or the integer location of the first match. In this case, there's little difference between the two.
1 Comment
gen_Eric
Shouldn't you order by
LOCATE('accessory', name) first? SELECT *,
(CASE WHEN `productname` LIKE '%accessory%' THEN 1 ELSE 0 END) AS `relevance`
FROM `tablename` ORDER BY `relevance`, `productname` ASC
You can give relevance based on conditions in mysql using case.
Obviously you can add your WHERE clause in there just before the ORDER BY statement, but for this example I just select everything
ORDER BYaccepts a comma-separated list of expressions.