Using ASC/DESC within an ORDER BY CASE()

What I need to do is order them from Classroom, Laboratories, Lecture Halls followed by Auditoriums. What I then need to do is to arrange them by their ID. So for the classrooms, I start from 1 and ascend accordingly.

The only fields I utilize are the roomID and the type column. (Should change type seeing as how it is an SQL function huh?)

SELECT * 
  FROM `rooms` 
ORDER BY CASE WHEN `type` = 'Classroom'
    THEN 1 
  WHEN `type` = 'Computer laboratory'
    THEN 2 
  WHEN `type` = 'Lecture Hall'
    THEN 3 
  WHEN `type` = 'Auditorium'
    THEN 4 
  END

It seem开发者_运维百科s simple enough, but I can't get it to work. So, any help would be greatly appreciated, especially since this is probable a stupid question.

What it seems like you should do is put that case statement into your query fields and give it an alias. Then set your order by statement to use that alias.

SELECT *,
CASE
WHEN type = 'Classroom' THEN 1
WHEN type = 'Computer laboratory' THEN 2
WHEN type = 'Lecture Hall' THEN 3
WHEN type = 'Auditorium' THEN 4
END AS ClassTypeValue
FROM rooms
ORDER BY ClassTypeValue

If I remember correctly this should work.

Just in case spinon's answer doesn't work for you can always make it an in-line view

SELECT * 
FROM
(

    SELECT *,
        CASE
        WHEN type = 'Classroom' THEN 1
        WHEN type = 'Computer laboratory' THEN 2
        WHEN type = 'Lecture Hall' THEN 3
        WHEN type = 'Auditorium' THEN 4
        END AS ClassTypeValue
    FROM rooms
) t
ORDER BY ClassTypeValue

SELECT *
FROM
    (
    SELECT *,
        CASE
        WHEN type = 'Classroom' THEN 1
        WHEN type = 'Computer laboratory' THEN 2
        WHEN type = 'Lecture Hall' THEN 3
        WHEN type = 'Auditorium' THEN 4
        END AS ClassTypeValue
    FROM rooms
    ) t
ORDER BY ClassTypeValue, maxppl, roomID

This is the final query of which I used. Thanks guys for helping me and explaining it to me.