name    student    teacher      special_teacher     special_student

a        x           x              1                     2
b        y           y              1                     2
a        x           x              1                     2
b        y           y              1                     2
a        x           x              1                     2
b        y           y              1                     2
a        x           x              1                     2
b        y           y              1                     2
a        x           x              1                     2
b        y           y              1                     2
a        x           x              1                     2
b        y           y              1                     2
a        x           x              1                     2
b        y           y              1                     2
a        x           x              1                     2
b        y           y              1                     2





SELECT *  FROM (
            SELECT
                `main_table`.*
            FROM
                student_teacher  AS  `main_table`
            WHERE
                STATUS =  "ENABLED"

            ORDER BY  student_teacher  =  "1"   DESC
        ) T1

ORDER BY  special =  "1"   DESC
        LIMIT 9