Order by Priority

[jimmyoneshot]

I have a page in my site which has a list of teachers I want them to be organised in a very speific way. Here is the query I have now that gets all the teachers.

 

$query_getteachers = "SELECT * FROM staffs WHERE type_id = 1 ORDER BY is_featured DESC, is_department_head DESC, join_date DESC LIMIT $start, $limit";

 

"is_featured" is a boolean (1 = true, 0 = false) which states whether the teacher is the featured teacher or not

"is_department_head" is a boolean (1 = true, 0 = false) which states whether the teacher is a department head or not

"join_date" is a date field which states when the teacher joined

"$start" and "$limit" are dynamic numeric values used for pagination purposes

 

Now the thing is I want it so that.

 

- The featured teacher is at the top (there is only 1 featured teacher)

- The department heads follow next ordered by their join date

- ONLY once the featured teacher and department heads have all been listed the rest of the teachers will be shown ordered by their creation date

 

This would usually be easily split into 3 queries but the need for pagination has complicated this somewhat so I need it to be done in one query. Is this doable and if so how? Because as the query is now my results all get mixed together. Thanks for any help in advance

[Simon Mayer]

That looks right to me. I can't see what is wrong.

It should sort first by `is_featured`(1 before 0), then `is_department_head` (1 before 0), then `join_date`(newest first)

 

What order do your results appear as?

[mikosiko]

Use :

ORDER BY (CASE....) DESC

 

read here:

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

 

ask again if you can't figure it out