Sort order on matching starting letters in words

Question

I have a table and I’m trying to filter values with “apple m” on name

select * from table where name like '%apple%m%'

+-------+-------------------------------+------------------------+
|  id   |             name              |          cat           |
+-------+-------------------------------+------------------------+
|  2757 | Apple MacBook                 | Laptops & Accessories  |
|  2777 | Apple Bottom Tops             | Western Wear           |
|  2752 | Apple Monitors                | Desktop Components     |
|  2756 | Apple Desktop & Monitors      | Desktops & Accessories |
|  2778 | Apple Bottom Tunics           | Tops                   |
|  2776 | Apple Selector & Smart Box    | Video & TV Accessories |
|  2787 | Apple Pie Pyjamas             | Girl Clothes           |
|  2773 | Apple Selector & Smart Box    | TV & Video Accessories |
|  2780 | Apple Fun Card Games          | Toys                   |
| 38304 | Snapple Mixer juicer grinders | Kitchen Appliances     |
+-------+-------------------------------+------------------------+

I want to sort display values which start with “apple” and “m” like below:

+------+--------------------------+------------------------+
|  id  |           name           |          cat           |
+------+--------------------------+------------------------+
| 2757 | Apple MacBook            | Laptops & Accessories  |
| 2752 | Apple Monitors           | Desktop Components     |
| 2756 | Apple Desktop & Monitors | Desktops & Accessories |
|      | ---Rest all after this-- |                        |
+------+--------------------------+------------------------+

Why do you have `Apple Desktop` after `Apple Monitors`? `D` comes before `M`. — Barmar, Nov 10 '15 at 01:00

score 1 · Accepted Answer · answered Nov 10 '15 at 01:02

1

Use:

ORDER BY name LIKE 'Apple%m%' DESC, name ASC

name LIKE 'Apple%m%' is 1 when name matches the pattern, 0 when it doesn't, so this will order the matching names first. Then it will order by the name within each of those groups.

answered Nov 10 '15 at 01:02

Barmar

741,623
53
500
612

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

I think you can use something simple like the following:

SELECT * FROM your_table WHERE name LIKE 'apple% m%' ORDER BY name;

The above query first will get rows with name starting by 'apple ' and then will order the results.

If you need case sensitivity then you can use:

SELECT * FROM your_table WHERE name LIKE 'Apple% m%' COLLATE utf8_bin ORDER BY name;

Case sensitivity in like

edited May 23 '17 at 12:30

Community

1
1

answered Nov 10 '15 at 00:33

Kostas Mitsarakis

4,772
3
23
37

Hi Kostas, If i do order by on name it will list 'Apple Bottom Tops' & 'Apple Bottom Tunics' first – Manoj Nov 10 '15 at 00:45
Oh, in your expected results you have: Apple Desktop & Monitors so for that reason i thought you wanted that. – Kostas Mitsarakis Nov 10 '15 at 00:47
Do you mean than you want only results that one word is 'Apple' and some other word starts with 'm' ? – Kostas Mitsarakis Nov 10 '15 at 00:48

Sort order on matching starting letters in words

2 Answers2