Column order

Question

I just wanted to order a list of candidates with my partisan and position. I used OR Clause, but it works, does not support many columns. this is my code: Help me please. Thanks

SELECT student.firstname, 
       candidates.partylist, 
       candidates.position 
FROM   student 
       INNER JOIN candidates 
               ON student.student_id = candidates.student_id 
ORDER  BY partylist, 
          position = 'President' 
           OR position = 'VicePresident' 
           OR position = 'Secretary' 
           OR position = 'Treasurer' 
           OR position = 'Auditor'; 

Answer 1

I think you need Casein Order byplace of conditions OR.

Try something like this

SELECT student.firstname, 
       candidates.partylist, 
       candidates.position 
FROM   student 
       INNER JOIN candidates 
               ON student.student_id = candidates.student_id 
ORDER  BY partylist, 
          CASE position 
            WHEN 'President' THEN 1 
            WHEN 'VicePresident' THEN 2 
            WHEN 'Secretary' THEN 3 
            WHEN 'Treasurer' THEN 4 
            WHEN 'Auditor' THEN 5 
            ELSE 6  -- Here replace 6 with 0
          END 

Note. If you want data other than the list of references in Order bycome first, replace 6with 0.

Answer 2

Use FIELD :

SELECT student.firstname, candidates.partylist, candidates.position 
FROM student 
INNER JOIN candidates ON student.student_id = candidates.student_id 
ORDER BY partylist, 
         FIELD(position, 'President', 'VicePresident', 'Secretary', 
               'Treasurer', 'Auditor');