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
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:
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.
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
Post a Comment