MySQL ORDER BY Clause
MySQL ORDER BY Clause: Sorting Query Results Deterministically
The MySQL ORDER BY clause sorts rows returned by a SELECT statement into a predictable order. Without ORDER BY, MySQL can return matching rows in any storage or index scan sequence, causing paginated tables and reports to shift unexpectedly.
Syntax and Sort Directions
Place ORDER BY after WHERE, GROUP BY, and HAVING, and before LIMIT:
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC, column2 DESC
LIMIT 20;
Ascending (ASC) is the default direction when omitted. Specify DESC for highest-to-lowest or newest-first ordering.
Multi-Column Sorting and NULL Ordering
When multiple rows share the first sort value, MySQL breaks ties with the next column in the ORDER BY list:
SELECT id, product_name, category, price
FROM demo_products
WHERE is_active = 1
ORDER BY category ASC, price DESC, id ASC;
- NULL handling: In
ASCorder,NULLvalues appear first; inDESCorder,NULLvalues appear last. - Deterministic pagination: Always append a unique tie-breaker column such as
, id ASCso rows with equal timestamps never jump across pages. - Index alignment: Composite indexes matching
(filter_col, sort_col)avoid slowUsing filesortpasses.
Frequently Asked Questions
Does MySQL evaluate ORDER BY before LIMIT?
Yes. MySQL sorts all filtered rows before applying LIMIT and OFFSET.
Why should I avoid ORDER BY RAND() on large tables?
ORDER BY RAND() assigns a random float to every row and sorts the full result set, causing high CPU and temporary table overhead.
Unlock Your
Full Potential.
Sign in to track your learning journey, earn industry-recognized certificates, and join our elite developer community.
Quick Access With
Try it Yourself
Experiment with the code from this lesson in our interactive playground.