Today I have been faced on situation where I need to display a list of users ordered by their name and their level. My table looks like below

id	first_name	last_nae	level
1	Muhammad	Salah	player
2	Joko	Tole	admin
3	Cristioano	Ronaldo	player
4	Zlatan	Ibrahimovic	player
5	Jose	Mourinho	coach
6	Mesut	Ozil	player
7	Pep	Guardiola	coach
8	Eden	Hazard	player
9	Petr	Cech	assistant manager

My task is to show the table sorted by name, but give a priority order as: admin, coach, assistant manager, player.

You know we just can not order by level column since the result will sorted alphabetically.

Using FIND_IN_SET Function

FIND_IN_SET is a string function I use to this solution. As documented on the page that:

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character.

MySQL official documentation

So instead of SELECT * FROM users ORDER BY first_name ASC, level ASC

My query should like this:

SELECT * 
FROM users
ORDER BY
    FIND_IN_SET(users.level, 'admin,coach,assistant manager,player'),
    first_name ASC

And the result as expected:

id	first_name	last_name	level
2	Joko	Tole	admin
5	Jose	Mourinho	coach
7	Pep	Guardiola	coach
9	Petr	Cech	assistant manager
3	Cristiano	Ronaldo	player
8	Eden	Hazard	player
6	Mesut	Ozil	player
1	Muhammad	Salah	player
4	Zlatan	Ibrahimovic	player

Happy querying 🎉🎉