CODEWITHSUNDEEP
mysqlsqlsorting
Colin Burns
Colin Burns

Reputation: 65

Sorting in MySQL using a CASE expression shows results I don't understand

I'm building a small program that allows teachers to create exams from a bank of questions. The teacher can then sort the questions in the order that he/she would like them to appear on the exam.

So I am trying to return a list of questions based on the order of record ID's.

SELECT * FROM ExamQuestion WHERE ElementID = 66

returned the following results...

2505
2506
2615
2616
2617

If I then try to sort these results based on the question order the teacher sets using the query below...

SELECT * FROM ExamQuestion WHERE ElementID = 66 ORDER BY 
CASE 
    WHEN ID = '2616' THEN '1'
    WHEN ID = '2615' THEN '2'
    WHEN ID = '2617' THEN '3'
    WHEN ID = '2505' THEN '4'
    ELSE ID END ASC

I get the following results

2616
2615
2506
2617
2505

This is almost perfect but what I can't work out is why 2506 is being returned before 2617 even though it is explicitly set.

Normally in this query I won't have a full list of ID's, it will vary depending on what questions the teacher would like to ask on the exam...

I would have thought that 2506 should appear last in the list...

Any thoughts?

Upvotes: 1

Views: 30

Answers (2)

Colin Burns
Colin Burns

Reputation: 65

Thanks guys - you were all spot on... The ordering was indeed done as a string. If I turn the THEN section of the CASE statement into numeric (remove the quotes around the number) then it works as I would expect

SELECT * FROM MightyMindsExamQuestion WHERE IFElementID = 66 ORDER BY 
CASE 
    WHEN ID = '2616' THEN 1
    WHEN ID = '2615' THEN 2
    WHEN ID = '2617' THEN 3
    WHEN ID = '2505' THEN 4
ELSE ID END ASC

Results

2616
2615
2617
2505
2506

Thanks for your help...

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

Here is your CASE statement:

CASE 
    WHEN ID = '2616' THEN '1'
    WHEN ID = '2615' THEN '2'
    WHEN ID = '2617' THEN '3'
    WHEN ID = '2505' THEN '4'
    ELSE ID END ASC

The value '2506' falls into the ELSE condition, which will sort by the ID value itself. In this case, the value is '2506', which falls in between the strings '2' and '3'.

Here is your result set next to which I list the values which were actually used to sort the results:

+------+------------+
|  ID  | Sort Value |
+------+------------+
| 2616 |   '1'      |
| 2615 |   '2'      |
| 2506 |   '2506'   |    <-- '2506' is in between '2' and '3'
| 2617 |   '3'      |
| 2505 |   '4'      |
+------+------------+

Upvotes: 3

Related Questions