MySQL ORDER BY NULL, FIELD and Custom Sort Order Explained

Where do NULLs go?

In MySQL, NULL is considered the lowest value. It comes first in ascending order and last in descending order:

query.sql

SQL

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(30) NOT NULL,
    due_on DATE NULL,
    priority VARCHAR(10) NOT NULL
);
INSERT INTO tasks (title, due_on, priority) VALUES
    ('Pay rent',     '2026-07-05', 'high'),
    ('Book tickets', NULL,         'low'),
    ('Reply to mail','2026-07-02', 'medium'),
    ('Clean desk',   NULL,         'low'),
    ('File taxes',   '2026-07-31', 'high');

SELECT title, due_on FROM tasks ORDER BY due_on;
SELECT title, due_on FROM tasks ORDER BY due_on DESC;

Output

OUTPUT

+---------------+------------+
| title         | due_on     |
+---------------+------------+
| Book tickets  | NULL       |
| Clean desk    | NULL       |
| Reply to mail | 2026-07-02 |
| Pay rent      | 2026-07-05 |
| File taxes    | 2026-07-31 |
+---------------+------------+
5 rows in set

+---------------+------------+
| title         | due_on     |
+---------------+------------+
| File taxes    | 2026-07-31 |
| Pay rent      | 2026-07-05 |
| Reply to mail | 2026-07-02 |
| Book tickets  | NULL       |
| Clean desk    | NULL       |
+---------------+------------+
5 rows in set

NULLs last in ascending order

Standard SQL has NULLS LAST. MySQL does not. The usual trick is to sort first by “is it NULL?”:

query.sql

SQL

SELECT title, due_on FROM tasks ORDER BY due_on IS NULL, due_on;

Output

OUTPUT

+---------------+------------+
| title         | due_on     |
+---------------+------------+
| Reply to mail | 2026-07-02 |
| Pay rent      | 2026-07-05 |
| File taxes    | 2026-07-31 |
| Book tickets  | NULL       |
| Clean desk    | NULL       |
+---------------+------------+
5 rows in set

due_on IS NULL is 0 for real dates and 1 for NULL, so the dated tasks come first (0), then the NULLs (1). Within each group, the second key due_on sorts normally.

For NULLs first in descending order, reverse it: ORDER BY due_on IS NOT NULL, due_on DESC.

A custom order with FIELD()

priority has a meaningful order (high, medium, low), which is not alphabetical. FIELD(value, list...) returns the position of the value in the list:

query.sql

SQL

SELECT title, priority FROM tasks ORDER BY FIELD(priority, 'high', 'medium', 'low'), id;

Output

OUTPUT

+---------------+----------+
| title         | priority |
+---------------+----------+
| Pay rent      | high     |
| File taxes    | high     |
| Reply to mail | medium   |
| Book tickets  | low      |
| Clean desk    | low      |
+---------------+----------+
5 rows in set

FIELD is MySQL-specific. It returns 0 for a value that is not in the list, which sorts first, so list every value.

The same thing with CASE (portable)

query.sql

SQL

1
2
3

SELECT title, priority
FROM tasks
ORDER BY CASE priority WHEN 'high' THEN 1 WHEN 'medium' THEN 2 ELSE 3 END, id;

Output

OUTPUT

+---------------+----------+
| title         | priority |
+---------------+----------+
| Pay rent      | high     |
| File taxes    | high     |
| Reply to mail | medium   |
| Book tickets  | low      |
| Clean desk    | low      |
+---------------+----------+
5 rows in set

The best way: a column that holds the order

FIELD() and CASE hard-code the order in each query. If the order is business data, put it in a table:

query.sql

SQL

CREATE TABLE priorities (name VARCHAR(10) PRIMARY KEY, sort_order TINYINT NOT NULL);
INSERT INTO priorities VALUES ('high', 1), ('medium', 2), ('low', 3);

SELECT t.title, t.priority
FROM tasks t
JOIN priorities p ON p.name = t.priority
ORDER BY p.sort_order, t.id;

Output

OUTPUT

+---------------+----------+
| title         | priority |
+---------------+----------+
| Pay rent      | high     |
| File taxes    | high     |
| Reply to mail | medium   |
| Book tickets  | low      |
| Clean desk    | low      |
+---------------+----------+
5 rows in set

Now the order is data. A new priority is a new row, with no query change.

Natural sorting: “v2” before “v10”

Text sorting puts v10 before v2. MySQL has no natural sort built in. For version-like values, extract the number:

query.sql

SQL

CREATE TABLE releases (version VARCHAR(10));
INSERT INTO releases VALUES ('v1'), ('v10'), ('v2'), ('v9'), ('v11');

SELECT version FROM releases ORDER BY version;
SELECT version FROM releases ORDER BY CAST(SUBSTRING(version, 2) AS UNSIGNED);

Output

OUTPUT

+---------+
| version |
+---------+
| v1      |
| v10     |
| v11     |
| v2      |
| v9      |
+---------+
5 rows in set

+---------+
| version |
+---------+
| v1      |
| v2      |
| v9      |
| v10     |
| v11     |
+---------+
5 rows in set

For real software versions (1.10.2), store each part in its own integer column, or build a sortable key with LPAD() or INET_ATON().

Random order

ORDER BY RAND() shuffles the result, but is slow on large tables (Random Rows).

lightbulb

Make the order data

A sort order that people may change (menu items, categories, “featured” products) belongs in a sort_order column that the application can edit.