CODEWITHSUNDEEP
mysqlsql-order-by
a coder
a coder

Reputation: 7639

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

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?

Upvotes: 0

Views: 2618

Answers (3)

Lawson
Lawson

Reputation: 634

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

Upvotes: -1

miah
miah

Reputation: 10433

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;

Upvotes: 1

John Woo
John Woo

Reputation: 263693

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

Upvotes: 3

Related Questions