ORDER OF USE

Question

I have a table with a column named role , and the values ​​are as follows:

Scientific staff PostDocs Supporting staff PNUT Visiting researchers Secretary Ph.D. students Students Other 

I want to use ORDER BY so that the Scientific staff comes first. At the moment when I make such a request, the fields of Ph.D. students will be returned first (well, because at the moment no row in the database has a field with Other ). Is there a way to achieve this using mysql or do I need to change the return values ​​manually? If so, could you tell me how?

 SELECT * FROM members ORDER BY role 

Answer 1

You can do this without an additional table and join, just do:

 SELECT * FROM members ORDER BY CASE role WHEN 'Scientific staff' THEN 1 WHEN 'PostDocs' THEN 2 WHEN 'Supporting staff' THEN 5 WHEN 'PNUT' THEN 6 WHEN 'Visiting researchers' THEN 4 WHEN 'Secretary' THEN 3 WHEN 'Ph.D. students' THEN 8 WHEN 'Students' THEN 7 WHEN 'Other' THEN 9 ELSE 10 END 

Answer 2

As at present, you cannot order in such a way that the scientific staff is the first, because the Oder execution sorts it in the order of the corresponding object (in your case, it is displayed in alphabetical order by role).

Now a simple solution is to build a sorting priority table with these values ​​in it, assigning a numerical priority, and then sorting by priority. Let me explain, which is slightly better, because it can be misleading:

You make a second table by listing each of these roles, and in the second column, highlight these roles with the priority that they will display.

Table: Role | Priority

Scientific staff | one

Candidate Students | 2

Other | 3 (etc.)

Then you can do something like this (pseudo-MySQL):

  select members.role from members inner join priority on members.role = priority.role order by priority.priority 

which will give you a role field from the participant table, sorted by priority set in the priority table. Since this is an internal join, anyone without a role will not be displayed.