How to Order MySQL Results with a Specific Field Value First?

Priority sorting of specific field values ​​in MySQL

In MySQL, you can display records with specific field values ​​preferentially. For example, if your table contains id, name, and priority columns, you might want to display records with name="core" first, regardless of their priority.

You can use MySQL's FIELD function to achieve this:

Full sorting of all values:

<code class="language-sql">SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core", "board", "other");</code>

This will sort the results in the order specified in the FIELD() parameter, with "core" first, "board" next, and "other" last.

Only "Core" values ​​are prioritized:

If you only want to prioritize "core" and don't care about the order of the other values:

<code class="language-sql">SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core") DESC;</code>

DESC order will ensure that the "core" line comes first.

Combine custom sorting and regular sorting:

Alternatively, you can combine custom sorting by "core" with regular sorting by other fields:

<code class="language-sql">SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core") DESC, priority;</code>

This will prioritize the "core" rows and then sort the remaining rows by priority.

Note:

• This feature is specific to MySQL.
• FIELD() returns a one-based index where missing values ​​result in zero.
• DESC ordering is required unless you specify all possible field values.

The above is the detailed content of How to Order MySQL Results with a Specific Field Value First?. For more information, please follow other related articles on the PHP Chinese website!