Mysql order by comma-delimited column value

Question

Hi Guys

I would like to know if it's possible to order by a comma-delimited set returned by a subquery?

I have the following:

SELECT navigation.id, documents.template_id
FROM navigation
INNER JOIN documents 
    ON navigation.document_id = documents.id
    AND FIND_IN_SET(navigation.id,(SELECT numeric_lineage 
                                     FROM navigation WHERE id = #id#))
ORDER BY ???

Now I'm not certain how I would go about ordering the results in the same order as the comma-delimited set in numeric_lineage. Is it possible? I would prefer not to have to do another subquery if possible.

Thanks

Answer 1

You can use FIND_IN_SET in the ORDER BY clause:

ORDER BY FIND_IN_SET(navigation.id, (SELECT numeric_lineage 
                                       FROM navigation WHERE id = #id#))