CODEWITHSUNDEEP
mysqlsorting
Kristian Rafteseth
Kristian Rafteseth

Reputation: 2032

Advanced sorting in mysql

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?

Upvotes: 2

Views: 635

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53830

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.

Upvotes: 5

Kai Qing
Kai Qing

Reputation: 18833

    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

Upvotes: 2

Related Questions