mysql - Query to retrieve records by aplhabetic order, except for n predefined items which must be on top -


i need retrieve records ordered alphabetically. except predefined list of record's columns records should appear first in given predefined order, other records should sorted alphabetically based on same column

for instance, assume have following table called names

enter image description here
lets assume predefined list ("mathew", "ashraf", "jack").
i.e. these names of whom records should listed first in predefined order.

so desired query result should be:

enter image description here

which query retrieve custom order ?
p.s, i'm using mysql.


here's trial based on comments' request:

(select * names name in ('mathew', 'ashraf', 'jack'))  union  (select * names name not in ('mathew', 'ashraf', 'jack') order name asc); 

the first query result wasn't ordered required.

enter image description here

 drop table if exists my_table;   create table my_table  (id int not null auto_increment primary key  ,name varchar(12) not null unique  );   insert my_table (name) values  ('sam'),  ('john'),  ('samanta'),  ('johny'),  ('ashraf'),  ('tom'),  ('jack'),  ('mathew'),  ('hans'),  ('adam');    select * my_table order field(name,'jack','ashraf','mathew') desc,name;  +----+---------+  | id | name    |  +----+---------+  |  8 | mathew  |  |  5 | ashraf  |  |  7 | jack    |  | 10 | adam    |  |  9 | hans    |  |  2 | john    |  |  4 | johny   |  |  1 | sam     |  |  3 | samanta |  |  6 | tom     |  +----+---------+ 

you can use find_in_set this...

 select * my_table order find_in_set(name,'jack,ashraf,mathew') desc,name; 

Comments

Popular posts from this blog

javascript - Jquery show_hide, what to add in order to make the page scroll to the bottom of the hidden field once button is clicked -

javascript - Highcharts multi-color line -

javascript - Enter key does not work in search box -