How to define a custom ORDER BY order in mySQL

MySQL

SQL Order By

Custom Sorting

Database Management

SQL Tutorial

How to define a custom ORDER BY order in mySQL

Master System Design with Codemia

Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.

Start Practicing Learn More

Introduction

MySQL's ORDER BY clause sorts results in ascending or descending order by default. When you need a custom sort order — like prioritizing specific statuses, sorting by a business-defined sequence, or applying conditional ordering — you can use FIELD(), CASE expressions, or calculated columns to define exactly how rows should be ordered.

Method 1: FIELD() Function

FIELD() returns the position of a value in a list, making it ideal for custom ordering:

sql

SELECT * FROM orders
ORDER BY FIELD(status, 'urgent', 'processing', 'pending', 'shipped', 'delivered');

This sorts by status in the exact order specified: urgent first, delivered last.

sql

1-- Custom priority for support tickets
2SELECT * FROM tickets
3ORDER BY FIELD(priority, 'critical', 'high', 'medium', 'low');
4
5-- Custom day ordering (Monday first)
6SELECT * FROM schedules
7ORDER BY FIELD(day_name, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');

Values not in the FIELD() list return 0 and sort first. To push unknown values to the end:

sql

1SELECT * FROM orders
2ORDER BY FIELD(status, 'urgent', 'processing', 'pending') DESC;
3-- Or:
4ORDER BY FIELD(status, 'urgent', 'processing', 'pending') = 0,
5         FIELD(status, 'urgent', 'processing', 'pending');

Method 2: CASE Expression

CASE provides the most flexibility for custom sorting:

sql

1SELECT * FROM orders
2ORDER BY
3    CASE status
4        WHEN 'urgent' THEN 1
5        WHEN 'processing' THEN 2
6        WHEN 'pending' THEN 3
7        WHEN 'shipped' THEN 4
8        WHEN 'delivered' THEN 5
9        ELSE 6
10    END;

Conditional Sorting with CASE

sql

1-- Sort active items first, then by date
2SELECT * FROM tasks
3ORDER BY
4    CASE WHEN is_active = 1 THEN 0 ELSE 1 END,
5    created_at DESC;
6
7-- Sort NULLs last (MySQL normally sorts NULLs first in ASC)
8SELECT * FROM products
9ORDER BY
10    CASE WHEN price IS NULL THEN 1 ELSE 0 END,
11    price ASC;

Method 3: ELT() and FIND_IN_SET()

Alternative functions for custom ordering:

sql

1-- FIND_IN_SET returns position in a comma-separated list
2SELECT * FROM orders
3ORDER BY FIND_IN_SET(status, 'urgent,processing,pending,shipped,delivered');
4
5-- ELT is the reverse of FIELD — returns the value at position N
6-- Less commonly used for ordering, but useful with numeric keys

Method 4: Join with a Sort Table

For complex or frequently changing sort orders, use a lookup table:

sql

1-- Create a sort order table
2CREATE TABLE status_order (
3    status VARCHAR(50) PRIMARY KEY,
4    sort_position INT
5);
6
7INSERT INTO status_order VALUES
8    ('urgent', 1), ('processing', 2), ('pending', 3),
9    ('shipped', 4), ('delivered', 5);
10
11-- Join to sort
12SELECT o.*
13FROM orders o
14LEFT JOIN status_order so ON o.status = so.status
15ORDER BY COALESCE(so.sort_position, 999);

This is the most maintainable approach for sort orders that change frequently or are managed by end users.

Multiple Sort Criteria

Combine custom ordering with standard sorting:

sql

1-- Custom status order, then by date within each status
2SELECT * FROM orders
3ORDER BY
4    FIELD(status, 'urgent', 'processing', 'pending', 'shipped'),
5    created_at DESC;
6
7-- Priority first, then alphabetical within each priority
8SELECT * FROM tasks
9ORDER BY
10    CASE priority
11        WHEN 'high' THEN 1
12        WHEN 'medium' THEN 2
13        WHEN 'low' THEN 3
14    END,
15    title ASC;

Custom Numeric Ordering

sql

1-- Sort version strings correctly (1.0, 2.0, 10.0 instead of 1.0, 10.0, 2.0)
2SELECT * FROM releases
3ORDER BY CAST(SUBSTRING_INDEX(version, '.', 1) AS UNSIGNED),
4         CAST(SUBSTRING_INDEX(version, '.', -1) AS UNSIGNED);
5
6-- Sort IP addresses
7SELECT * FROM servers
8ORDER BY INET_ATON(ip_address);

Dynamic Custom Ordering

Use a parameter to switch sort direction:

sql

1-- Sort ascending or descending based on a variable
2SET @sort_dir = 'DESC';
3
4SELECT * FROM products
5ORDER BY
6    CASE WHEN @sort_dir = 'ASC' THEN price END ASC,
7    CASE WHEN @sort_dir = 'DESC' THEN price END DESC;

Common Pitfalls

FIELD() returns 0 for missing values: Values not in the FIELD() list get position 0, which sorts them before position 1. Handle unknown values explicitly or push them to the end.
Performance: FIELD() and CASE in ORDER BY prevent MySQL from using indexes for sorting. For large tables, consider adding a sort_order column and indexing it.
Case sensitivity: FIELD() uses the column's collation for comparison. If your column is case-insensitive (utf8_general_ci), FIELD(status, 'Urgent') matches 'urgent'. For case-sensitive matching, use BINARY.
NULL handling: FIELD(NULL, ...) returns 0. NULLs sort first in ascending order by default. Use CASE WHEN col IS NULL THEN 999 END to push NULLs last.
Enum columns: If the column is an ENUM type, MySQL stores values as integers internally. ORDER BY status on an ENUM already uses the declaration order, which may be what you want.

Summary

Use FIELD(col, 'val1', 'val2', ...) for simple custom ordering of known values
Use CASE WHEN ... THEN N END for conditional or complex sort logic
Use a join with a sort-order lookup table for maintainable, changeable sort orders
Values not in FIELD() return 0 and sort first — handle explicitly
Custom ORDER BY expressions prevent index usage — add a sort_order column for large tables