Using ORDER BY FIELD and another (second) ORDER clause in one query

Question

This question asks about specifying a sort order in MySQL.

What I'd like to do is have two levels of sort - the first would be my specified order, then the remaining, non-specified values would be sorted in the normal way.

What I tried is:

    SELECT
        version_id,
        version_name
    FROM
        software_versions
    ORDER BY 
        FIELD(version_id, 133, 41, 88), 
        version_name ASC

But what happens is the rows are sorted by version name, then FIELD (backwards from how the query is structured).

Is it possible to do what I am asking? The lookup table is relatively small (100 or so rows) so I'm not worried too much about the performance hit from using ORDER BY FIELD.

Do I need to rework this as a UNION?

Do you have version ids 133, 41 and 88? And can we assume versionid is a number field? — gbn, Apr 16 '13 at 15:35
Add the FIELD clause to the SELECT list: what does it give you? — gbn, Apr 16 '13 at 15:36

score 3 · Accepted Answer · answered Apr 16 '13 at 15:36

3

try to reverse the order so 0 will always be in the last position.

ORDER BY FIELD(version, 88, 41, 133) DESC,  version_name ASC

SQLFiddle Demo

answered Apr 16 '13 at 15:36

John Woo

258,903
69
498
492

score 1 · Answer 2 · answered Apr 16 '13 at 15:41

I found this article explaining it. basically, because you haven't specified all of the version numbers, it won't work. The work around is to sort by the field function, then the field itself.

ORDER BY FIELD(version, 88, 41, 133) DESC, version, version_name;

score -1 · Answer 3 · answered Apr 16 '13 at 15:43

It's because you haven't listed all of the possible values for version_id in your FIELD() function.

This should work though:

SELECT
    version_id,
    version_name
FROM
    software_versions
WHERE
    version_id IN (133, 41, 88)
ORDER BY 
    FIELD(version_id, 133, 41, 88), 
    version_name ASC

Using ORDER BY FIELD and another (second) ORDER clause in one query

3 Answers3