Mysql Query List order in LIKE

Question

My Table Structure is

id | name 
--------
1    Test name

2    Test name1

3    Name1

4    name2 

5    Name6

6    Name7

7    AA Name1

8   AA Name2

if am using query

select name from table like '%na%' order by name asc limit 5;

Result will be

AA Name1

AA Name2

Name1

Name2

Name6

I need a query to show following result

Name1

Name2

Name6

Name7

AA Name1

Because i've table If i typed p i need result perl, php, phython before 'apple script'

I've a table contains same name repeating rows like bob smith, bob morgan, bob rals, alias bob, steward bob, rals steward etc. if i type bob i need bob starting name will come first after alias bob will show example [link](https://erail.in/) type madu in from — Yuvaraj Jeganathan, Apr 06 '17 at 14:18
This will give you the expected result (at least for your example data and without the stars of course). select name from table like '%na%' order by id asc limit 5; But I don't understand your intention behind this. — GreenTurtle, Apr 06 '17 at 14:25
you may refer use locate function in order by http://stackoverflow.com/questions/18725941/mysql-order-by-best-match — Ranjith R, Apr 08 '17 at 07:23

fqhv · Accepted Answer · 2017-04-06T14:38:21.767

1

You could use a CASE in your ORDER BY to determine if the record starts with na.

SELECT name 
FROM table 
WHERE name LIKE '%na%'
ORDER BY CASE WHEN name LIKE 'na%' THEN 0 ELSE 1 END, name
LIMIT 5;

Or you could order by the location of na in the record by using INSTR.

SELECT name 
FROM table 
WHERE name LIKE '%na%'
ORDER BY INSTR(name,'na'), name
LIMIT 5;

edited Apr 06 '17 at 14:38

answered Apr 06 '17 at 14:15

fqhv

1

The case should be the opposite, since 0 sorts before 1. – jarlh Apr 06 '17 at 14:35

1 Answers1