Issue with GROUP_CONCAT AND ORDER BY FIELD in MySQL query

I'm having some issues with a query. I would like to ORDER a query BY a number of id's. Here is my query:

This query doesn't return the result as I would like :(

SELECT *
FROM question q
INNER JOIN answer a ON a.question_id = q.question_id
WHERE q.lesson_id = 1
AND q.question_id IN (
    SELECT e.question_id
    FROM exame e
    WHERE e.inscription_id = 1
    AND e.lesson_id = 1
    AND e.attempt = 1
    ORDER BY e.question_id
) /*this subquery returns (10,2,1,8,3,12,4,11,14,7)*/
ORDER BY FIELD(q.question_id,(
    SELECT GROUP_CONCAT(DISTINCT ee.question_id ORDER BY ee.order_of_appearance SEPARATOR ',') AS final_order
    FROM exame ee
    WHERE ee.inscription_id = 1
    AND ee.lesson_id = 1
    AND ee.attempt = 1)
) /*this subquery returns (10,2,1,8,3,12,4,11,14,7)*/

As you can see, the two subqueries return the same result (10,2,1,8,3,12,4,11,14,7). As you know, the difference between them is that the first one returns a resultset and the second one only a field with all the id's concatenated.

Issue #1: If I copy the first subquery and write it in the place where the second one is located, I get this error:

1242 - Subquery returns more than 1 row

So I've created the second subquery (with the GROUP_CONCAT function), but the result isn't what I was expecting. The result is ordered by "question_id" and I want that it has ordered by "order_of_appearance" field.

Issue #2: If I write the subquery inside the ORDER BY clause, I don't get the result ordered by "order_of_appearance" field, but if I delete the subquery and I manually write the array of id's, the result is ordered by "order_of_appearance"!! WHY???

This query returns the result as I would like!! :)

SELECT *
FROM question q
INNER JOIN answer a ON a.question_id = q.question_id
WHERE q.lesson_id = 1
AND q.question_id IN (
    SELECT e.question_id
    FROM exame e
    WHERE e.inscription_id = 1
    AND e.lesson_id = 1
    AND e.attempt = 1
    ORDER BY e.question_id
)
ORDER BY FIELD(q.question_id,10,2,1,8,3,12,4,11,14,7)

Final question: Is it possible to achieve what I want without having to write manually the array of id's? I need to do it dinamically.

Thanks in advance! (I hope you understand my english!)

Solution

I think that you need to use FIND_IN_SET() instead of FIELD():

From the reference manual:

FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.

If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT().
mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
    -> 2

mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
    -> 0

FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
    -> 2